如何通过键从JSON字符串中提取值 [英] How to extract a value from a JSON string by key

问题描述

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

I have a column which has Json string records. I want to parse the json and fetch the value of a particular key from within the select statement.

示例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"的值.我尝试使用Regex和Substring，但是由于json的长度不是固定的，因此无法获取所有记录的正确值.

I want my select query to fetch only the value of the key "confidence". I tried using Regex and Substring, but since the json length is not fixed, it doesn't fetch correct values for all the records.

我尝试了这些

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

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

我的MYSQL版本不支持Json_extract().

The Json_extract() isn't supported with my MYSQL version.

感谢帮助.

推荐答案

MySQL已在5.7.7版中支持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/

您必须纯粹在mysql中解析它，否则恐怕您必须将其视为字符串并从中切出值(仅是正常的字符串函数或使用正则表达式).这不是很优雅，但可以正常工作

You have to resolive 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

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;

此查询在jsondata中搜索Confidence，然后在置信度之后查看下一个分隔符，并将其减去这两个索引之间的内容.

This query search for Confidence in your jsondata, then look at the next separator after confidence, and it substract the content between these two index.

这是上面示例的SQL小提琴: http://sqlfiddle.com/#！9/2edfaf/3/0

Here's a SQL fiddle of the example above: http://sqlfiddle.com/#!9/2edfaf/3/0

这篇关于如何通过键从JSON字符串中提取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！