How to get values from MySQL(5.6) column if that contains json document as string

Question

How to get values from MySQL(5.6) column if that contains JSON document as a string

For example, if we have a table - employee in that we have three columns id, name and educations. and column educations contains data as a JSON document

{"ug":"bsc","pg":"mca","ssc":"10th"}

I need the value of ug and pg from educations column

Can we do that using MySQL(5.6) queries?

MySQL doesn't know anything about JSON data. To it, it's just a blob of data. You'll need to decode the JSON *after* retrieving it from the database (using whatever language you're using to access the db). — gen_Eric, Jun 14 '16 at 15:45
It looks like MySQL 5.7 adds JSON support: https://dev.mysql.com/doc/refman/5.7/en/json.html#json-paths — gen_Eric, Jun 14 '16 at 15:46

Alvaro Flaño Larrondo · Answer 1 · 2016-06-14T15:58:38.353

To be able to do what you want to, you need MySQL 5.7.8+. Since 5.7.8 you can use JSON_EXTRACT function to extract a value from a JSON string:

SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');

+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

Taken from here.

In MySQL 5.6 you just can't get the value you want as MySQL doesn't know anything about what a JSON object is. So your options are:

Upgrade to 5.7.8+
Parse the query result with something that handles JSON:
- Could be PHP json_decode (or equivalent in your language)
- An online tool like http://json.parser.online.fr/

score 21 · Answer 2 · answered May 19 '17 at 10:03

21

In MySQL 5.6, by default JSON_EXTRACT is not available by default.
If you still need to access json data in MySQL 5.6, you need to write custom function.

DELIMITER $$

DROP FUNCTION IF EXISTS `json_extract_c`$$

CREATE DEFINER=`root`@`%` FUNCTION `json_extract_c`(
  details TEXT,
  required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
  RETURN TRIM(
    BOTH '"' FROM SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        SUBSTRING_INDEX(
          details,
          CONCAT(
            '"',
            SUBSTRING_INDEX(required_field,'$.', - 1),
            '"'
          ),
          - 1
        ),
        '",',
        1
      ),
      ':',
      - 1
    )
  ) ;
END$$

DELIMITER ;

This will help. I have created it and tested.

answered May 19 '17 at 10:03

Rahul

18,271
7
41
60

4

The return value contains "}" at the end plus given: {"distance":55.62,"totalDistance":11946.83} JSON_EXTRACT_C(attributes, "$.distance") gives 11946.83} – user3631341 Sep 06 '17 at 07:13
check your data, I am using this function as a replacement from past few months with not a single issue at all. – Rahul Sep 06 '17 at 07:16
Can you test using my data? – user3631341 Sep 06 '17 at 07:38
Yes I am checking with your data – Rahul Sep 06 '17 at 08:35
I used this function, and the first call returned the correct data. But the next ones returned the same result with a different json key. Any idea ? – E.K Feb 08 '18 at 17:58
Can you explain why you need the `CREATE DEFINER` bit? – Burhan Ali Apr 23 '18 at 12:39
3

This is not particularly robust, or correct. `select json_extract_c('{"a": 1, "b": 2}', "$.a");` -> `2`, `select json_extract_c('{"a": "1"}', "$.a")` -> `"1`, `select json_extract_c('{"a": "a"}', "$.a")` -> empty string. The other answers that provide hand rolled functions are also based on this one, so ‍♂️ – Burhan Ali Apr 23 '18 at 14:58
2

@gaborsch 's answer solved all above examples, should be checked as the correct answer – ayehia Dec 02 '18 at 11:37

score 15 · Answer 3 · answered Sep 07 '17 at 06:30

Both previous answers didn't work for me when the element wasn't mentioned in JSON text. There is my improved function:

DELIMITER $$

DROP FUNCTION IF EXISTS `json_extract_c`$$

CREATE FUNCTION `json_extract_c`(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
  DECLARE search_term TEXT;
  SET details = SUBSTRING_INDEX(details, "{", -1);
  SET details = SUBSTRING_INDEX(details, "}", 1);
  SET search_term = CONCAT('"', SUBSTRING_INDEX(required_field,'$.', - 1), '"');
  IF INSTR(details, search_term) > 0 THEN
    RETURN TRIM(
      BOTH '"' FROM SUBSTRING_INDEX(
        SUBSTRING_INDEX(
          SUBSTRING_INDEX(
            details,
            search_term,
            - 1
          ),
          ',"',
          1
        ),
        ':',
        -1
      )
    );
  ELSE
    RETURN NULL;
  END IF;
END$$

DELIMITER ;

The above does not work: select json_extract_c('{"foo":"fing", "bar":"bing"}',"$.foo") returns "bing" when it should return "fing" — Severun, Dec 27 '19 at 22:52
My provided function doesn't look at spaces after coma. It will work with '{"foo":"fing","bar":"bing"}', but not '{"foo":"fing", "bar":"bing"}'. Check out @gnought answer for more advanced realisation. — Janis Baiza, Jan 09 '20 at 18:01

gaborsch · Answer 4 · 2019-02-26T09:13:37.430

Here are my 3 SQL stored functions that I use for JSON extracting. They handle nested objects, but only care about the key name. The keys must be strings, the values can be strings, numeric or boolean values. Arrays are not treated well, only the first value is picked. They return NULL if no value found.

The first one, json_extract_1 picks only the first value if there are multiple keys with the same name. If you leave the LIMIT 1 clause, it throws 'Subquery returns more than 1 row' if more keys found (safe mode).

The second, json_extract_m collects all values with the same key in a comma-separated list.

The third one, json_extract_c is the slowest one, but it treats values with commas also correctly. Use it if it's absolutely necessary, e.g textual descriptions.

For all three the limitation is 999 keys. You can speed up if you prepare a table for the numbers subselect.

DELIMITER $$

/*
 * Single-value JSON extract - picks the first value
 */
DROP FUNCTION IF EXISTS `json_extract_1`$$
CREATE FUNCTION `json_extract_1`(json_txt TEXT, search_key VARCHAR (255)) 
    RETURNS TEXT
BEGIN
    RETURN (SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(txt,':',-1), '"', 2), '"', -1)) AS val
    FROM (
        SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( SUBSTRING_INDEX(json_txt , ',', n), ',',  -1 ), '}', 1), '{', -1)) AS txt
        FROM (SELECT t1.v + t2.v*10 + t3.v*100 AS n
            FROM (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
            (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
            (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3) numbers
        WHERE CHAR_LENGTH(json_txt ) - CHAR_LENGTH(REPLACE(json_txt , ',', '')) >= n - 1
        AND n>0 ) sp
    WHERE TRIM(SUBSTRING_INDEX(txt,':',1)) = CONCAT('"',search_key,'"')
    LIMIT 1   -- comment out for safe mode
    );
END$$

/*
 * Multi-value JSON extract - collects all values, group_concats them with comma
 */
DROP FUNCTION IF EXISTS `json_extract_m`$$
CREATE FUNCTION `json_extract_m`(json_txt TEXT, search_key VARCHAR (255)) 
    RETURNS TEXT
BEGIN
    RETURN (SELECT GROUP_CONCAT(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(txt,':',-1), '"', 2), '"', -1))) AS val
    FROM (
        SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( SUBSTRING_INDEX(json_txt , ',', n), ',',  -1 ), '}', 1), '{', -1)) AS txt
        FROM (SELECT t1.v + t2.v*10 + t3.v*100 AS n
            FROM (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
            (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
            (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3) numbers
        WHERE CHAR_LENGTH(json_txt ) - CHAR_LENGTH(REPLACE(json_txt , ',', '')) >= n - 1
        AND n>0 ) sp
    WHERE TRIM(SUBSTRING_INDEX(txt,':',1)) = CONCAT('"',search_key,'"'));
END$$

/*
 * Comma-safe JSON extract - treats values with commas correctly, but slow like hell
 */
DROP FUNCTION IF EXISTS `json_extract_c`$$
CREATE FUNCTION `json_extract_c`(json_txt TEXT, search_key VARCHAR (255)) 
    RETURNS TEXT
BEGIN
    DROP TEMPORARY TABLE IF EXISTS json_parts;
    DROP TEMPORARY TABLE IF EXISTS json_parts2;
    DROP TEMPORARY TABLE IF EXISTS json_indexes;

    CREATE TEMPORARY TABLE json_parts AS
    SELECT n, IF(INSTR(txt,':')>0 AND (INSTR(txt,',')+INSTR(txt,'{')>0),1,0) AS this_val, IF(INSTR(txt,':')>0 AND (INSTR(txt,',')+INSTR(txt,'{')=0),1,0) AS next_val, IF(INSTR(txt,',')+INSTR(txt,'{')>0,1,0) AS next_key, txt
    FROM (SELECT n, IF(n%2,txt,REPLACE(txt,',','|')) AS txt 
    FROM (SELECT n, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(json_txt , '"', n), '"',  -1 )) AS txt
        FROM (SELECT t1.v + t2.v*10 + t3.v*100 AS n
        FROM (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
        (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
        (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3) numbers
        WHERE CHAR_LENGTH(json_txt ) - CHAR_LENGTH(REPLACE(json_txt , '"', '')) >= n - 1
        AND n>0) v
        ) v2;
    CREATE TEMPORARY TABLE json_parts2 AS SELECT * FROM json_parts;

    CREATE TEMPORARY TABLE json_indexes AS
    SELECT p1.n, p1.n+1 AS key_idx, MIN(GREATEST(IF(p2.this_val,p2.n,0), IF(p2.next_val,p2.n+1,0))) AS val_idx, p2.this_val AS trim_val
    FROM json_parts p1
    JOIN json_parts2 p2 ON (p1.n < p2.n AND (p2.this_val OR p2.next_val)) 
    WHERE p1.next_key
    GROUP BY p1.n;  

    RETURN (SELECT json_values.v 
        FROM (SELECT p1.txt AS k, REPLACE(IF(i.trim_val, regexp_replace(regexp_replace(p2.txt,'^[: {]+',''),'[, }]+$',''), p2.txt), '|', ',') AS v
            FROM json_indexes i
            JOIN json_parts p1 ON (i.key_idx = p1.n)
            JOIN json_parts2 p2 ON (i.val_idx = p2.n)) json_values
        WHERE json_values.k = search_key);
END$$

DELIMITER ;

Yep, and if you have the chance, try to upgrade to MySQL 5.7, the built-in functions work much more efficiently.

Best solution, actually the only one with no issues till now, thanks @gaborsch — ayehia, Dec 02 '18 at 11:35
Setting limit to numbers improve efficiency: `DECLARE keys_num INT; SET keys_num = LENGTH(json_txt) - LENGTH(REPLACE(json_txt, ',', '')) +1;` and `... t3 LIMIT keys_num) numbers ...` — BugaBuga, Feb 22 '22 at 14:35

score 4 · Answer 5 · edited Jun 25 '19 at 18:51

Rahul's answer did not work quite well for me, so I edited it and this worked for me:

DELIMITER $$

DROP FUNCTION IF EXISTS `json_extract_c`$$

CREATE FUNCTION `json_extract_c`(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
SET details = TRIM(LEADING '{' FROM TRIM(details));
SET details = TRIM(TRAILING '}' FROM TRIM(details));
RETURN TRIM(
    BOTH '"' FROM SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            SUBSTRING_INDEX(
                details,
                CONCAT(
                    '"',
                    SUBSTRING_INDEX(required_field,'$.', - 1),
                    '":'
                ),
                - 1
            ),
            ',"',
            1
        ),
        ':',
        -1
    )
) ;
END$$

DELIMITER ;

Rok Furlan · Answer 6 · 2019-04-23T11:26:56.163

Function above not work properly if you have nested JSON in table field.

Because i needed a JSON_EXTRACT on mysql 5.6 i wrote it by myself a copy of original function that can extract values like the native function in mysql 5.7

Usage:

SELECT JSON_EXTRACT_NESTED(table_field,"json_level1.json_level2.json_level3") FROM table;

If you have one level JSON then you use:

SELECT JSON_EXTRACT_NESTED(table_field,"json_level1") FROM table;

In database you have to add two functions:

Main function:

CREATE FUNCTION `json_extract_nested`(
_field TEXT,
_variable TEXT
) RETURNS TEXT CHARSET latin1
BEGIN
                DECLARE X INT DEFAULT 0;
                DECLARE fieldval1 TEXT;
                DECLARE arrayName,arrayValue TEXT;

                SET arrayName = SUBSTRING_INDEX(_variable, '.', 1);

                IF(LOCATE('%',arrayName)> 0) THEN 
                    SET _field = SUBSTRING_INDEX(_field, "{", -1);
                    SET _field = SUBSTRING_INDEX(_field, "}", 1);
                    RETURN TRIM(
                    BOTH '"' FROM SUBSTRING_INDEX(
                        SUBSTRING_INDEX(
                        SUBSTRING_INDEX(
                            _field,
                            CONCAT(
                            '"',
                            SUBSTRING_INDEX(_variable,'$.', - 1),
                            '":'
                            ),
                            - 1
                        ),
                        ',"',
                        1
                        ),
                        ':',
                        -1
                    )
                    ) ;
                ELSE  
                    SET arrayValue = json_array_value(_field, arrayName);
                    WHILE X < (LENGTH(_variable) - LENGTH(REPLACE(_variable, '.', ""))) DO
                        IF(LENGTH(_variable) - LENGTH(REPLACE(_variable, '.', ""))>X) THEN
                            SET arrayName = SUBSTRING_INDEX(SUBSTRING_INDEX(_variable, '.', X+2),'.',-1);
                        END IF;
                        IF(arrayName<>'') THEN
                            SET arrayValue = json_array_value(arrayValue, arrayName);
                        END IF;
                        SET X = X + 1;
                    END WHILE;
                END IF;
                RETURN arrayValue;
                END$$
DELIMITER ;

Auxiliary function (needed by main function):

CREATE FUNCTION `json_array_value`(
  _field  TEXT,
  arrayName VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
                DECLARE arrayValue, arrayValueTillDelimit TEXT;
                DECLARE arrayStartDelimiter, arrayEndDelimiter VARCHAR(10);
                DECLARE arrayCountDelimiter INT;
                DECLARE countBracketLeft, countBracketRight INT DEFAULT 0;
                DECLARE X INT DEFAULT 0;
                DECLARE arrayNameQuoted VARCHAR(255);
                SET arrayNameQuoted = CONCAT('"',arrayName,'"');
                /*check arrayname exist*/
                IF(LOCATE(arrayNameQuoted,_field)= 0) THEN 
                    RETURN NULL;    
                ELSE
                    /*get value behind arrayName1*/
                    SET _field = SUBSTRING(_field,1,LENGTH(_field)-1);
                    SET arrayValue = SUBSTRING(_field, LOCATE(arrayNameQuoted,_field)+LENGTH(arrayNameQuoted)+1, LENGTH(_field));
                    /*get json delimiter*/
                    SET arrayStartDelimiter = LEFT(arrayValue, 1);
                    IF(arrayStartDelimiter='{') THEN
                        SET arrayEndDelimiter = '}';                            
                        loopBrackets: WHILE X < (LENGTH(arrayValue)) DO                 
                            SET countBracketLeft = countBracketLeft +IF(SUBSTRING(arrayValue,X,1)=arrayStartDelimiter,1,0);
                        SET countBracketRight = countBracketRight +IF(SUBSTRING(arrayValue,X,1)=arrayEndDelimiter,1,0);
                        IF(countBracketLeft<>0 AND countBracketLeft=countBracketRight) THEN
                            SET arrayCountDelimiter = X;
                            LEAVE loopBrackets;
                        ELSE
                            SET X = X + 1;
                        END IF;
                        END WHILE;
                                ELSEIF(arrayStartDelimiter='[') THEN
                                    SET arrayEndDelimiter = ']';
                                    SET arrayCountDelimiter = LENGTH(SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, 0));
                                 ELSEIF(arrayStartDelimiter='"') THEN
                                    SET arrayEndDelimiter = '"';
                                    SET arrayCountDelimiter = LENGTH(SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, 0));
                                ELSE 
                                    SET arrayStartDelimiter = "";
                                    IF((LOCATE(",",arrayValue)> LOCATE("}",arrayValue))) THEN
                        SET arrayEndDelimiter = ",";
                        ELSE
                        SET arrayEndDelimiter = "}";
                        END IF;
                        SET arrayCountDelimiter = LENGTH(SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, 0));
                    END IF;
                    SET arrayValueTillDelimit = SUBSTRING(arrayValue, 1, arrayCountDelimiter);
                    SET arrayCountDelimiter = LENGTH(arrayValueTillDelimit) - LENGTH(REPLACE(arrayValueTillDelimit, arrayStartDelimiter, ""));
                    SET arrayValue = SUBSTR(arrayValue,LENGTH(arrayStartDelimiter)+1);
                    IF(arrayStartDelimiter='{') THEN
                        SET arrayValue = SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, arrayCountDelimiter);
                    ELSE
                        SET arrayValue = SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, arrayCountDelimiter+1);
                    END IF;
                    RETURN (arrayValue);
                END IF;
                END$$
DELIMITER ;

if anyone is planning to crash his server, can try it. execution: 6 s 577 ms — Julfikar, Nov 04 '20 at 08:08

Gnought · Answer 7 · 2019-11-19T06:13:08.633

This is our deployed json_extract_c in MySQL 5.6

Tested:

select json_extract_c('{"a": 1, "b": 2}', "$.a"); -> 1;
select json_extract_c('{"a": "1", "b": "2"}', "$.a"); -> 1;
select json_extract_c('{"a":"1","b":"2"}', "$.a"); -> 1;
select json_extract_c('{"a"    :"1",  "b" :"2"}', "$.a"); -> 1;
select json_extract_c('{"b"    :"a",  "a" :"2"}', "$.a"); -> 2;
select json_extract_c('{"a"    : "a",  "a" :"2"}', "$.a"); -> a;
select json_extract_c('{"a": "1"}', "$.a"); -> 1
select json_extract_c('{"a": "a"}', "$.a"); -> a
select json_extract_c('{"a"   : "a"}', "$.a"); -> a
select json_extract_c('{"a.a"   : "a"}', "$.a"); -> NULL
select json_extract_c('{"a\"a"   : "9"}', "$.a"); -> NULL

Not supported:

Nested json
Escaped double quotes in json values, e.g. select json_extract_c('{"a\"a" : "9", "a" : "a\"a"}', "$.a"); -> a

Limitation:

Only first field will be extracted if there are multiple same fields, e.g. select json_extract_c('{"a" : 1, "a" : "2}', "$.a"); -> 1

DELIMITER $$
DROP function if exists json_extract_c$$
CREATE FUNCTION json_extract_c(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET utf8mb4
DETERMINISTIC
NO SQL
BEGIN
  DECLARE search_term, val TEXT;
  DECLARE pos INT signed DEFAULT 1;

  -- Remove '{' and '}'
  SET details = SUBSTRING_INDEX(details, "{", -1);
  SET details = SUBSTRING_INDEX(details, "}", 1);
  -- Transform '$.xx' to be '"xx"'
  SET search_term = CONCAT('"', SUBSTRING_INDEX(required_field,'$.', - 1), '"');

  searching: LOOP
    SET pos = LOCATE(search_term, details);
    -- Keep searching if the field contains escape chars
    WHILE pos > 0 AND RIGHT(LEFT(details, pos-1), 1) = '\\'
    DO
      SET details = SUBSTR(details, pos+LENGTH(search_term));
      SET pos = LOCATE(search_term, details);
    END WHILE;
    -- Return NULL if not found
    IF pos <= 0 THEN
      RETURN NULL;
    END IF;

    SET pos = LENGTH(search_term)+pos;
    SET details = SUBSTR(details, pos);
    SET val = TRIM(details);

    -- see if we reach the value that is a leading colon ':'
    IF LEFT(val, 1) = ':' THEN
      RETURN TRIM(
        TRAILING ',' FROM 
        TRIM(
          SUBSTRING_INDEX(
            TRIM(
              BOTH '"' FROM TRIM(
                SUBSTR(
                  val
                , 2
                )
              )
            )
          , '"', 1
          )
        )
      );
    ELSE
      ITERATE searching;
    END IF;
  END LOOP;
END$$
DELIMITER ;

This works for few rows. In case of multiple row it takes much longer time and query doesn't complete — Vicky Thakor, Jul 07 '20 at 05:15

score 1 · Answer 8 · answered Sep 09 '19 at 15:22

Offering this alternative view of the answers given here for those of you (like me) who may not intuitively see the string manipulation within the SQL functions. This version will allow you to explicitly see each step of the text parsing. This works for MySQL 5.6 and can of course be combined back together and not use any variables.

DELIMITER $$ 
DROP FUNCTION IF EXISTS `json_extract_c`$$
CREATE FUNCTION `json_extract_c`(
  details TEXT,
  required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
  /* get key from function passed required field value */
  set @JSON_key = SUBSTRING_INDEX(required_field,'$.', -1); 
  /* get everything to the right of the 'key = <required_field>' */
  set @JSON_entry = SUBSTRING_INDEX(details,CONCAT('"', @JSON_key, '"'), -1 ); 
  /* get everything to the left of the trailing comma */
  set @JSON_entry_no_trailing_comma = SUBSTRING_INDEX(@JSON_entry, ",", 1); 
  /* get everything to the right of the leading colon after trimming trailing and leading whitespace */
  set @JSON_entry_no_leading_colon = TRIM(LEADING ':' FROM TRIM(@JSON_entry_no_trailing_comma)); 
  /* trim off the leading and trailing double quotes after trimming trailing and leading whitespace*/
  set @JSON_extracted_entry = TRIM(BOTH '"' FROM TRIM(@JSON_entry_no_leading_colon));
  RETURN @JSON_extracted_entry;
END$$
DELIMITER ;

score 0 · Answer 9 · answered Apr 04 '19 at 05:34

Below answer works for me. it remove double quotes from the value.

DELIMITER $$

DROP FUNCTION IF EXISTS `json_extract_values`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `json_extract_values`(
  details TEXT,
  required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
SET details = SUBSTRING_INDEX(details, "{", -1);
SET details = SUBSTRING_INDEX(details, "}", 1);
RETURN 
    SUBSTRING_INDEX(
        TRIM(
            TRAILING '"' FROM 
                SUBSTRING_INDEX(
                    details,
                    CONCAT( 
                           '"', 
                           SUBSTRING_INDEX(required_field,'$.', - 1),
                           '":'
                          ),
                -1 )
            ),
        '"',
     -1);    
END$$

DELIMITER ;

score 0 · Answer 10 · answered Aug 24 '21 at 02:10

Use SUBSTRING_INDEX function like below:

select * from (select id, name, 
    substring_index(substring_index(educations, "ug\":\"", -1), "\"", 1) as ug, 
    substring_index (substring_index(educations, "pg\":\"", -1), "\"", 1) as pg
 from employee) as t1;

Let me know if it works. You can add conditions also based on the above columns you got.

score 0 · Answer 11 · answered Sep 10 '21 at 04:19

Late to the Party.

As of now we know that json supports newer versions of mysql and mariaDB. (from mysql 5.7+ and mariaDB 10.2+)

But we can still see some places using the old mysql version 5.6. Eg: Some shared hosting providers.

So, this is my version of the alternative method for JSON_EXTRACT().

USE CASE:

select * From offer;
+----------+-------------------------------+-----------------------+
| offer_id | terms                         | name                  |
+----------+-------------------------------+-----------------------+
|        1 | {"qty": 3, "total": 600.00}   | x                     |
|        2 | {"qty": 2, "discount": 15.00} | Buy 2 GET ONE 15% OFF |
|        3 | {"discount": 9.09}            | Buy 1 SAVE 10/-       |
+----------+-------------------------------+-----------------------+
4 rows in set (0.001 sec)

SELECT offer_id
     , terms
     , json_extract_c(terms, 'qty') as offer_qty  
     , json_extract_c(terms, 'discount') as offer_discount
     , json_extract_c(terms, 'total') as offer_total
FROM offer; 

+----------+-------------------------------+-----------+----------------+-------------+
| offer_id | terms                         | offer_qty | offer_discount | offer_total |
+----------+-------------------------------+-----------+----------------+-------------+
|        1 | {"qty": 3, "total": 600.00}   | 3         | NULL           | 600.00      |
|        2 | {"qty": 2, "discount": 15.00} | 2         | 15.00          | NULL        |
|        3 | {"discount": 9.09}            | NULL      | 9.09           | NULL        |
+----------+-------------------------------+-----------+----------------+-------------+
4 rows in set (0.003 sec)

Custom function:

DELIMITER $$
CREATE FUNCTION `json_extract_c`(
  target VARCHAR(50)
, jkey VARCHAR(50)
) RETURNS varchar(10) CHARSET utf8
BEGIN
DECLARE newtarget VARCHAR(50) DEFAULT trim(leading '{' from trim(trailing '}' from target));
DECLARE targa VARCHAR(50) DEFAULT '';
DECLARE thekey VARCHAR(50);
DECLARE theval VARCHAR(10);

WHILE LENGTH(newtarget) > 0 DO
    SET targa = substring_index(newtarget, ',', 1);
    IF LOCATE(',', newtarget) > 0 THEN
        SET newtarget = substring_index(newtarget, ',', -1);
    ELSE   
        SET newtarget = '';
    END IF;
    SET thekey = substring_index(targa, ':', 1);
    SET thekey = TRIM(BOTH '"' FROM TRIM(thekey));
    SET theval = substring_index(targa, ':', -1);
    IF thekey = jkey THEN
        RETURN TRIM(theval);
    END IF;
END WHILE;
RETURN NULL;
END$$

score 0 · Answer 12 · answered Feb 02 '22 at 20:17

If the json document is saved as string, you can get all values of the json fields by the following functions

JSON_EXTRACT(JSON_UNQUOTE(<field-name>), "$.*")

JSON_UNQUOTE will parse the json from the string, then JSON_EXTRACT will extract values based on the given path. "$.*" indicates all keys of the json object.

score 0 · Answer 13 · answered Jun 08 '22 at 13:03

0

I have used common_schema for parsing JSON in older MySQL versions.
https://code.google.com/archive/p/common-schema/
https://github.com/shlomi-noach/common_schema

answered Jun 08 '22 at 13:03

Paul

1
2

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 08 '22 at 14:44
While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/31971345) – lemon Jun 10 '22 at 10:10

score -1 · Answer 14 · answered Jun 23 '20 at 08:31

Yes , you can definitely to it using JSON_EXTRACT() function in mysql.

lets take a table that contains JSON (table client_services here) :

+-----+-----------+--------------------------------------+
| id  | client_id | service_values                       |
+-----+-----------+------------+-------------------------+
| 100 |      1000 | { "quota": 1,"data_transfer":160000} |
| 101 |      1000 | { "quota": 2,"data_transfer":800000} |
| 102 |      1000 | { "quota": 3,"data_transfer":70000}  |
| 103 |      1001 | { "quota": 1,"data_transfer":97000}  |
| 104 |      1001 | { "quota": 2,"data_transfer":1760}   |
| 105 |      1002 | { "quota": 2,"data_transfer":1060}   |
+-----+-----------+--------------------------------------+

To Select each JSON fields , run this query :

SELECT 
    id, client_id, 
    json_extract(service_values, '$.quota') AS quota,
    json_extract(service_values, '$.data_transfer') AS data_transfer
FROM client_services;

So the output will be :

+-----+-----------+----------------------+
| id  | client_id | quota | data_transfer|
+-----+-----------+----------------------+
| 100 |      1000 |     1 |       160000 |
| 101 |      1000 |     2 |       800000 |
| 102 |      1000 |     3 |        70000 |
| 103 |      1001 |     1 |        97000 |
| 104 |      1001 |     2 |         1760 |
| 105 |      1002 |     2 |         1060 |
+-----+-----------+----------------------+

Hope this solves your problem!

Question specifies 5.6 clearly where JSON_EXTRACT() isn't available. — rainabba, Dec 07 '20 at 20:15

How to get values from MySQL(5.6) column if that contains json document as string

14 Answers14

Linked