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

Answer 1

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 |
+-----+-----------+----------------------+

NOW, if you want lets say DISTINCT quota , then run this query :

SELECT 
   distinct( JSON_EXTRACT(service_values, '$.quota')) AS quota
FROM client_services;

So this will result into your desired output :

+-------+
| quota |
+-------+
|     1 |
|     2 |
|     3 |
+-------+

hope this helps!

Answer 2

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

Answer 3

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.