SingleStore (MemSQL) how to parse json field to column

Question

I have a table source

id	json
1	{"some_id":{"some_text_1":"90","some_text_2":"50","some_text_3":"30"}}
2	{"some_id":{"some_text_3":"90","some_text_4":"50"}}

I would like to get something like this

text	value
"some_text_1"	90
"some_text_2"	50
"some_text_3"	30
"some_text_3"	90
"some_text_4"	50

How can I achieve this? Thanks

Answer 1

You can use OPENJSON function in SQL Server. To transform the JSON data into the format you specified:

SELECT
    j.[key] AS [text],
    j.[value] AS [value]
FROM
    source s
CROSS APPLY
    OPENJSON(s.json, '$.some_id') AS j
ORDER BY
    [text];

---

CROSS APPLY
    OPENJSON(s.json, '$.some_id') AS j

OPENJSON parses the JSON within the "some_id" property, creating a table-valued function that produces rows with the "key" and "value" columns.

To be able to use OPENJSON, ensure your database has a minimum compatibility level of 130.

If not you may see the following error:

Invalid object name 'OPENJSON'.

Then you need to do the following:

1/ Check the compatibility of your database:

SELECT compatibility_level
FROM sys.databases
WHERE name = 'DatabaseName';

2/ If it is not >= 130 then:

ALTER DATABASE DatabaseName
SET COMPATIBILITY_LEVEL = 150; 

---

Here is the result: