在MySQL中解析JSON [英] Parse JSON in MySQL
问题描述
我需要有关如何在MySQL中解析JSON数据的帮助.
I need help on how to parse JSON data in MySQL.
我可以解析名为config的列,其中包含以下数据:
I can parse a column named config containing data such as:
{"encounterId":"f45bf821-98e1-4496-82ef-047971e168cb","providerId":"38001853-d2e1-4361-9fff-cfca1aedf406","patientId":"f4d04edb-652f-427c-ac25-6fecbda2a0aa","obs":[{"conceptId":"4e903795-ad79-48fc-851e-9e67c9628e6b","value":0.0},{"conceptId":"5300c3e4-3b53-4a0b-874b-3060d18cec9b","value":"Q"},{"conceptId":"dded4485-6160-4791-a13d-16c87f5004dc","value":"000019"},{"conceptId":"4e503f63-caa0-419a-8670-112441d228da","value":"girl"}],"dateCreated":"Dec 5, 2012 9:39:01 AM","formId":"ETAT","locationId":"","created":1354693141902}
通过使用
select common_schema.get_option(be.config,'encounterid') AS eid
, common_schema.get_option(be.config,'providerid') AS gender
, common_schema.get_option(be.config,'patientid') AS pid
from bencounter be
得到我所需要的.
但是,我无法获取"obs"的数据,这是字段conceptid和value的几个行".
However, I am unable to get the data for 'obs' which is several 'rows' of the fields conceptid and value.
在obs的集合"返回空值之后,对字段的任何引用进一步
Further more any reference to a field after the 'set' of obs returns a null
select common_schema.get_option(be.config,'encounterid') AS eid
, common_schema.get_option(be.config,'providerid') AS gender
, common_schema.get_option(be.config,'patientid') AS pid
, common_schema.get_option(be.config,'formId') AS formid -- THIS RETURNS NULL
from bencounter be
有人可以帮我解决这个问题吗?
Can some one please help me figure this out.
我想直接在MySQL中解决这个问题...
I would like to solve this directly in MySQL...
克莱门斯
推荐答案
这是MySQL 5.7语法中的一种解决方案:
Here's a solution in MySQL 5.7 syntax:
select be.config->'$.encounterId' AS eid
, be.config->'$.providerId' AS gender
, be.config->'$.patientId' AS pid
, be.config->'$.formId' AS formid
from bencounter be \G
输出:
*************************** 1. row ***************************
eid: "f45bf821-98e1-4496-82ef-047971e168cb"
gender: "38001853-d2e1-4361-9fff-cfca1aedf406"
pid: "f4d04edb-652f-427c-ac25-6fecbda2a0aa"
formid: "ETAT"
请记住,JSON中的字段键区分大小写.例如,'formId'
与'formid'
不同.
Remember that field keys in JSON are case-sensitive. For example, 'formId'
is not the same as 'formid'
.
这篇关于在MySQL中解析JSON的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!