如何通过键从JSON字符串中提取值

Question

我可以帮助您翻译。以下是需要翻译的内容：

我有一个包含Json字符串记录的列。我想解析Json并从select语句中提取特定键的值。

示例JSON

{"kofaxDocId":"8ae16f46-c68f-11e5-8105-0e15fb39b661","systemDocType":"Loan Application","requestId":"c770a940-b9f3-4c41-aee6-3e08c1470ec6","docType":"Loan Application","confidence":0.6499999761581421,"engineType":"kofax","completionStatus":"Suggested"}

我希望我的选择查询仅获取关键字“confidence”的值。我尝试使用正则表达式和子字符串，但由于JSON长度不固定，它无法正确获取所有记录的值。

我尝试了以下方法：

SELECT substring(extended_metadata, ('"confidence":', extended_metadata ))  FROM documents ;

SELECT json_extract(extended_metadata,'confidence') CONFIDENCE from documents;

我的MYSQL版本不支持Json_extract()函数。

谢谢帮忙。

- Maverick

---

你的MySQL版本是什么？你能轻松升级它吗？ - Kobi

Answer 1

MySQL在5.7.7版本中开始支持JSON。如果您需要在MySQL中处理JSON，则需要将其视为字符串并从中提取值（使用普通的字符串函数或正则表达式）。这种方法并不优雅，但是可以解决问题。有关更多信息，请参见http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/。

CREATE TABLE testjson (`jsonfield` varchar(2000)) ;

INSERT INTO testjson (`jsonfield`) VALUES ('{"kofaxDocId":"8ae16f46-c68f-11e5-8105-0e15fb39b661","systemDocType":"Loan Application","requestId":"c770a940-b9f3-4c41-aee6-3e08c1470ec6","docType":"Loan Application","confidence":0.6499999761581421,"engineType":"kofax","completionStatus":"Suggested"}')  ;


SELECT substring(jsonfield, locate('"confidence":',jsonfield)+13, locate(',"', jsonfield, locate('"confidence":',jsonfield))-locate('"confidence":',jsonfield)-13) as confidence_value
  FROM testjson;

这个查询在你的json数据中搜索置信度，然后查看置信度后面的下一个分隔符，并减去这两个索引之间的内容。

以下是上述示例的SQL fiddle: http://sqlfiddle.com/#!9/2edfaf/3/0

- Kobi

Answer 2

我不得不修改Kobi的脚本，因为如果值位于JSON文件的末尾，子字符串将失败，因为它正在寻找 ',"'。此脚本将提取一个字符串。

在脚本中用列标题的长度替换15。（例如=“referred-by”：）

用列标题替换“referred-by”。

SELECT  substring(jsonfield, locate('"referred-by":',jsonfield)+15, (locate('"', jsonfield, locate('"referred-by":',jsonfield)+15))-(locate('"referred-by":',jsonfield)+15)) as referredby

- Richard Dev