0

I have a database which uses JSON to store values. 

CREATE TABLE JSON(name TEXT, value TEXT);

I am trying to convert this into a native format.

CREATE TABLE NATIVE(name TEXT, KEY1, KEY2, KEY3);

The JSON format looks like this:

[
    {"key1":value1, "key2":value2, "key3":value3},
    {"key1":value4, "key2":value5, "key3":value6},
    ....
]

For the above example, I am trying to come up with a query using INSERT INTO NATIVE (name, KEY1, KEY2, KEY3) SELECT <something> FROM JSON to produce this table:

+------+---------+--------+--------+
| TEXT | KEY1    | KEY2   | KEY3   |
+------+---------+--------+--------+
| TEXT | VALUE1  | VALUE2 | VALUE3 |
| TEXT | VALUE4  | VALUE5 | VALUE3 |
...
+------+---------+--------+--------+

I have been using JSON1 for other tables which use simple objects. So for instance when I have values which are objects and not arrays of objects I can use json_extract for each field.

For an array I think I am supposed to use json_each but I am having a hard time figuring out how to apply it to this specific problem.

Michael
  • 9,060
  • 14
  • 61
  • 123

2 Answers2

1

I came up with this solution:

INSERT INTO NATIVE (name, key1, key2, key3)
SELECT name, json_extract(x.value, '$.key1') 
           , json_extract(x.value, '$.key2')
           , json_extract(x.value, '$.key3')
FROM JSON, json_each(JSON.value) AS x;

The trick is that json_each when used in conjunction with the table containing JSON and a SELECT is returning rows in which there are fields called key and value which contain each key and value. It is then possible to call json_extract in the select to pick individual fields out, which can then be inserted into the new table.

Michael
  • 9,060
  • 14
  • 61
  • 123
0

We can try using the JSON_EXTRACT function from the json1 extension library, along with an INSERT INTO ... SELECT:

INSERT INTO NATIVE(name TEXT, KEY1, KEY2, KEY3)
SELECT
    name,
    JSON_EXTRACT(value, '$.key1'),
    JSON_EXTRACT(value, '$.key2'),
    JSON_EXTRACT(value, '$.key3')
FROM JSON;

This assumes that it is the value column in JSON which contains the raw JSON. If not, then replace value in the above query by whatever column contains the JSON content.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • this works if the value contains an object. however, the value contains an array of objects. i think i've stumbled on a possible solution though, i'll post it shortly. – Michael May 30 '19 at 04:25