从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}

我想选择所有不同的城市值。只能使用mysql服务器。

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已经在版本5.7.7中得到了对JSON的支持
http://mysqlserverteam.com/json-labs-release- native-json-data-type-and-binary-format /
您将能够使用jsn_extract函数来有效地解析您的JSON字符串。

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.

如果你有一个较老的版本，并且你想纯粹用mysql来解决它，那么恐怕你必须把它当作一个字符串并且将它的值减掉（只是普通的字符串函数或者使用正则表达式）
这不是优雅，但它会工作

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

从mysql字段中提取json中的数据

相关推荐