Clever Geek Handbook

Extract data from json inside mysql field

I have a table with rows and one of the rows has a data field like this

{"name":"Richard","lastname":null,"city":"Olavarria","cityId":null}

And I want to select all the distinct "city" values ​​that I have. Only using mysql server.

Is it possible? I am trying with something like this

SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"]*)key_word([^"]*)"';

But I can't seem to get regexp to work

Thank you in advance

+4


source to share


4 answers


MySQL got JSON support in version 5.7.7 http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/ You can use the jsn_extract function to parse your JSON string efficiently.

If you have an older version and want to solve it exclusively in mysql, then I'm afraid you should treat it as a string and strip the value out of it (just regular string functions or use regex) This is not elegant but will work



http://sqlfiddle.com/#!9/97cfd/14

SELECT
  DISTINCT(substring(jsonfield, locate('"city":',jsonfield)+8,
     locate('","', jsonfield, locate('"city":',jsonfield))-locate('"city":',jsonfield)-8)
  )
FROM
  ForgeRock
+9


source


I wrapped this in a stored function for those limited to MySQL and lt; 5.7.7:

CREATE FUNCTION `json_extract_string`(
    p_json text,
    p_key text
) RETURNS varchar(40) CHARSET latin1
BEGIN
    SET @pattern = CONCAT('"', p_key, '":"');
    SET @start_i = LOCATE(@pattern, p_json) + CHAR_LENGTH(@pattern);
    if @start_i = CHAR_LENGTH(@pattern) then
        SET @end_i = 0;
    else
        SET @end_i = LOCATE('"', p_json, @start_i) - @start_i;
    end if;
    RETURN SUBSTR(p_json, @start_i, @end_i);
END


Note that this only works with string values, but is more reliable than @DmitryK's answer, since it returns an empty string if the key is not found, and the key can be anywhere in the JSON string.

+4


source


See MariaDB Dynamic Columns .

Also, search this forum for [mysql] [json]; the topic was often discussed.

0


source


It might be a little late, but the accepted answer doesn't work for me. I have used SUBSTRING_INDEX to achieve the desired result.

SELECT 
    ID, SUBSTRING_INDEX(SUBSTRING_INDEX(JSON, '"mykey" : "', -1), '",', 1) MYKEY
FROM MY_TABLE;

Hope this helps.

0


source






More articles:


All Articles