mysql 提取json 记录,从mysql字段中提取json中的数据

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

解决方案

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

SELECT

DISTINCT(substring(jsonfield, locate('"city":',jsonfield)+8,

locate('","', jsonfield, locate('"city":',jsonfield))-locate('"city":',jsonfield)-8)

)

FROM

ForgeRock