Extract data from json inside mysql field

Question

I've got a a table with rows, and one of the rows has a field with data like this

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

And i want to select all the distinct "city" values i've got. Only using mysql server.

Is it possible? I'm trying with something like this

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

But i can't make the regexp work

Thanks in advance

Solution 1

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

If you have an older version and you want to solve it purely in mysql then I am afraid you have to treat it as a string and cut the value out of it (just normal string functions or use regular expressions) This is not elegant but it 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

Solution 2

I have wrapped this into a stored function for those constrained to MySQL <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 this only works with string values but is a bit more robust than @DmitryK's answer, in that it returns an empty string if the key is not found and the key can be anywhere in the JSON string.

Comments

[styks]

This is very limited. Does not cover numbers which don't have ".

[AwokeKnowing]

nice. in my case I offset 1 character more 9 (instead of 8) to handle if it's: "city": "x" vs "city":"x" so depending on the space it ends up with a "x quote in front, and I have a trim to remove it.: trim(leading '"' from substring( pj.config, locate('"accountId":',pj.config)+13, locate('","', pj.config, locate('"accountId":',pj.config)) - locate('"accountId":',pj.config)-13 )) account_id