Parse JSON in MySQL

Question

I need help on how to parse JSON data in MySQL.

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}

by using

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

to get what I need.

However, I am unable to get the data for 'obs' which is several 'rows' of the fields conceptid and value.

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.

I would like to solve this directly in MySQL...

Clemens

You're already using some weird hacky extended syntax. Why not use a database suited for this? — ceejayoz, Nov 06 '14 at 01:16
Thanks. The data is already in a MySQL database which I inherited. I have to find a way to parse the data. What can I do given this situation? — user3726118, Nov 06 '14 at 18:58
Pull out the JSON and parse it in your app. Trying to do this in MySQL is folly and prone to disaster. — ceejayoz, Nov 06 '14 at 19:06

score 9 · Answer 1 · answered Jan 30 '17 at 00:29

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

Output:

*************************** 1. row ***************************
   eid: "f45bf821-98e1-4496-82ef-047971e168cb"
gender: "38001853-d2e1-4361-9fff-cfca1aedf406"
   pid: "f4d04edb-652f-427c-ac25-6fecbda2a0aa"
formid: "ETAT"

Remember that field keys in JSON are case-sensitive. For example, 'formId' is not the same as 'formid'.

Great... I was almost going to install the UFD for parsing JSON. This saved me from that... Just confused about the performance of the query when using this. — TheCleverIdiot, Jun 12 '18 at 06:06

score 1 · Answer 2 · edited Apr 12 '16 at 10:26

1

It appears you are using https://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/get_option.html. It specifies that subdictionaries are not supported, which I think is your problem here.

Mysql is not a great tool for parsing JSON.

I think there are some efforts for future versions like 5.7 to start including some support for JSON (see http://blog.ulf-wendel.de/2014/mysql-5-7-http-plugin-mysql/).

If you are on an earlier version now you might try using UDFs like http://www.slideshare.net/mobile/SvetaSmirnova/mysql-json-functions

HTH

edited Apr 12 '16 at 10:26

LUIS PEREIRA

478
4
20

answered Nov 06 '14 at 01:25

Joe Murray

585
5
21

Thanks. What could I use to quickly parse this data? – user3726118 Nov 06 '14 at 18:56

score 0 · Answer 3 · answered Jan 29 '17 at 23:55

Either you could use a cumbersome MySQL UDF for parsing JSON for MySQL like for example https://github.com/ChrisCinelli/mysql_json

...but a better way would be to pull out the JSON and parse it in your app, and perhaps convert the data to a more suited schema for your intentions.

Parse JSON in MySQL

3 Answers3

Linked