从SQLite的JSON对象列表中提取指定部分数据

现有表结构 #

CREATE TABLE tbl 
(
    id INT PRIMARY KEY,  
    data JSON
); 

插入数据示例 #

INSERT INTO tbl (id, data) 
VALUES 
(1, '[
             {
                "name": "object 1",
                "key": "value 1",
                "unwanted key": "unwanted value 1"
             },
             {
                "name": "object 2",
                "key": "value 2",
                "unwanted key": "unwanted value 2"
             }
         ]'),
(2, '[
             {
                "name": "object 1",
                "key": "value 1",
                "unwanted key": "unwanted value 1"
             },
             {
                "name": "object 2",
                "key": "value 2",
                "unwanted key": "unwanted value 2"
             }
         ]');

需求说明 #

• 提取每个JSON对象的name和key字段，剔除unwanted key
• 保留JSON列表结构
• 通过id字段区分多行数据

解决方案 #

利用SQLite的JSON函数组合实现：先通过json_each遍历每行的JSON数组，再用json_object构造只包含目标字段的新对象，最后按id分组，用json_group_array将单个对象重新组合为JSON列表。

执行以下SQL查询：

SELECT 
    t.id,
    json_group_array(
        json_object(
            'name', json_extract(j.value, '$.name'),
            'key', json_extract(j.value, '$.key')
        )
    ) AS data
FROM tbl t
JOIN json_each(t.data) j
GROUP BY t.id;

查询结果 #

内容的提问来源于stack exchange，提问作者JuA