Извлечение массивов JSON и вставка их в виде отдельных строк в новую базу данных.

Question

У меня есть база данных, которая использует JSON для хранения значений.

CREATE TABLE JSON(name TEXT, value TEXT);

Я пытаюсь преобразовать это в родной формат.

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

Формат JSON выглядит следующим образом:

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

Для приведенного выше примера, я пытаюсь придумать запрос, используя INSERT INTO NATIVE (name, KEY1, KEY2, KEY3) SELECT <something> FROM JSON произвести эту таблицу:

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

Я использовал JSON1 для других таблиц, которые используют простые объекты. Так, например, когда у меня есть значения, которые являются объектами, а не массивами объектов, я могу использовать json_extract для каждого поля.

Для массива я думаю, что я должен использовать json_each но мне трудно понять, как применить это к этой конкретной проблеме.

Answer 1

Я придумал это решение:

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;

Хитрость в том, что json_each при использовании вместе с таблицей, содержащей JSON, а SELECT возвращает строки, в которых есть поля, называемые key а также value которые содержат каждый ключ и значение. Тогда можно позвонить json_extract в select, чтобы выбрать отдельные поля, которые затем могут быть вставлены в новую таблицу.

Answer 2

Мы можем попробовать использовать JSON_EXTRACT функция от json1 библиотека расширения, наряду с 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;

Это предполагает, что это value колонка в JSON который содержит сырой JSON. Если нет, то заменить value в приведенном выше запросе по любому столбцу содержит содержимое JSON.