时间戳

首页 > 解决方案 > 提取 JSON 数组并将它们作为单独的行插入到新数据库中

sqlite - 提取 JSON 数组并将它们作为单独的行插入到新数据库中

问题描述

我有一个使用 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但我很难弄清楚如何将其应用于这个特定问题。

标签： sqlitesqlite-json1

解决方案

我想出了这个解决方案：

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_extractselect 来选择单个字段，然后可以将其插入到新表中。

推荐阅读