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

我想选择我所拥有的所有不同的“城市”价值观。只使用mysql服务器。

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

提前致谢

3 个解决方案

#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.

MySQL在版本5.7.7中支持JSON http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/您将能够有效地使用jsn_extract函数解析您的JSON字符串。

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

如果你有一个旧的版本,并且你想纯粹在mysql中解决它,那么我担心你必须把它当作一个字符串并从中削减它(只是正常的字符串函数或使用正则表达式)这不是优雅但它将工作

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

#2

I have wrapped this into a stored function for those constrained to MySQL <5.7.7:

我把它包装成一个存储函数,用于那些受限于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.

请注意,这仅适用于字符串值,但比@DmitryK的答案更健壮,因为如果找不到密钥则返回空字符串,并且密钥可以位于JSON字符串中的任何位置。

#3

See MariaDB's Dynamic Columns.

请参阅MariaDB的动态列。

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

另外,在这个论坛上搜索[mysql] [json];这个话题经常被讨论过。

#1