我有一列包含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”的值。我尝试使用Regex和Substring,但是由于json长度不是固定的,因此无法获取所有记录的正确值。
我尝试了这些
SELECT substring(extended_metadata, ('"confidence":', extended_metadata )) FROM documents ; SELECT json_extract(extended_metadata,'confidence') CONFIDENCE from documents;
我的MYSQL版本不支持Json_extract()。
感谢帮助。
MySQL已在5.7.7版中支持JSON http://mysqlserverteam.com/json-labs-release-native- json-data-type-and-binary-format/
您必须纯粹在mysql中解析它,否则恐怕您必须将其视为字符串并将其值切掉(仅是正常的字符串函数或使用正则表达式)。这不是很优雅,但可以正常工作
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,然后查看confidence之后的下一个分隔符,并将这两个索引之间的内容相减。
这是上面示例的SQL小提琴:http : //sqlfiddle.com/#!9/2edfaf/3/0