CODEWITHSUNDEEP
sqlitesqlite-json1
Michael
Michael

Reputation: 9402

Extracting JSON arrays and inserting them as separate rows in a new database

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.

Upvotes: 0

Views: 894

Answers (2)

Michael
Michael

Reputation: 9402

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.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

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.

Upvotes: 0

Related Questions