json - 当sqlite json具有数组值时如何正确提取

Question

我有一个 sqlite 数据库，并且在其中一个字段中存储了完整的 json 对象。我必须做一些 json 选择请求。如果你看到我的json
ALL 键的值是一个数组。 我们需要提取一些数据，例如“pod”字段为 fb 的所有评论。 当sqlite json有值时如何正确提取作为数组 ?

从数据表中选择 json_extract(data,'$."json"') ；给我全部。然后我做
选择 json_extract(data,'$."json"[0]') 但我不想手动执行。我想迭代。

请建议一些我可以学习和工作的来源。
我的 JSON

{
    "ALL": [{
            "comments": "your site is awesome",
            "pod": "passcode",
            "originalDirectory": "case1"
        },
        {
            "comments": "your channel is good",
            "data": ["youTube"],
            "pod": "library"
        },
        {
            "comments": "you like everything",
            "data": ["facebook"],
            "pod": "fb"
        },
        {
            "data": ["twitter"],
            "pod": "tw",
            "ALL": [{
                "data": [{
                    "codeLevel": "3"
                }],
                "pod": "mo",
                "pod2": "p"
            }]
        }
    ]
}



create table datatable ( path string , data json1 );
insert into datatable values("1" , json('<abovejson in a single line>'));

Answer 1

简单列表

在您的 JSON 表示“简单”评论列表的地方，您需要类似的内容:

select key, value 
  from datatable, json_each( datatable.data, '$.ALL' )
 where json_extract( value, '$.pod' ) = 'fb' ;

它使用您的示例数据返回:

2|{"comments":"you like everything","data":["facebook"],"pod":"fb"}

json_each()的使用为输入 JSON ( datatable.data ) 的每个元素返回一行，从路径 $.ALL 开始(其中 $ 是顶层， ALL 是数组的名称:如果需要 JSON 对象的顶层，则可以省略路径)。在您的情况下，这将为每个评论条目返回一行。

此行的字段记录在 4.13. The json_each() and json_tree() table-valued functions在 SQLite 文档中:我们感兴趣的两个是 key (非常粗略地说，“行号”)和 value (当前元素的 JSON)。后者将包含名为 comment 的元素和 pod ， 等等..

因为我们只对 pod 所在的元素感兴趣。等于 fb ，我们添加一个 where条款，使用 json_extract()到 pod (其中 $.pod 相对于 value 由 json_each 函数返回)。

嵌套列表

如果您的 JSON 包含嵌套元素(我一开始没有注意到这一点)，那么您需要使用 json_tree()函数代替 json_each() .而后者只会迭代指定节点的直接子节点， json_tree()将通过指定节点的所有子节点递归下降。

为了给我们一些数据来处理，我用一个额外的元素增加了你的测试数据:

create table datatable ( path string , data json1 );
insert into datatable values("1" , json('
    {
        "ALL": [{
                "comments": "your site is awesome",
                "pod": "passcode",
                "originalDirectory": "case1"
            },
            {
                "comments": "your channel is good",
                "data": ["youTube"],
                "pod": "library"
            },
            {
                "comments": "you like everything",
                "data": ["facebook"],
                "pod": "fb"
            },
            {
                "data": ["twitter"],
                "pod": "tw",
                "ALL": [{
                    "data": [{
                        "codeLevel": "3"
                    }],
                    "pod": "mo",
                    "pod2": "p"
                },
                {
                    "comments": "inserted by TripeHound",
                    "data": ["facebook"],
                    "pod": "fb"
                }]
            }
        ]
    }
'));

如果我们简单地切换到使用 json_each() ，然后我们看到一个简单的查询(没有 where 子句)将返回源 JSON 的所有元素:

select key, value 
  from datatable, json_tree( datatable.data, '$.ALL' ) limit 10 ;

ALL|[{"comments":"your site is awesome","pod":"passcode","originalDirectory":"case1"},{"comments":"your channel is good","data":["youTube"],"pod":"library"},{"comments":"you like everything","data":["facebook"],"pod":"fb"},{"data":["twitter"],"pod":"tw","ALL":[{"data":[{"codeLevel":"3"}],"pod":"mo","pod2":"p"},{"comments":"inserted by TripeHound","data":["facebook"],"pod":"fb"}]}]
0|{"comments":"your site is awesome","pod":"passcode","originalDirectory":"case1"}
comments|your site is awesome
pod|passcode
originalDirectory|case1
1|{"comments":"your channel is good","data":["youTube"],"pod":"library"}
comments|your channel is good
data|["youTube"]
0|youTube
pod|library

因为 JSON 对象混合了简单的值，我们不能再简单地添加 where json_extract( value, '$.pod' ) = 'fb'因为这会在 value 时产生错误不代表对象。解决此问题的最简单方法是查看 type json_each() 返回的值/ json_tree() :这些将是字符串 object如果该行表示一个 JSON 对象(有关其他值，请参阅上面的文档)。

将此添加到 where子句(并依靠“短路评估”来防止 json_extract() 在非对象行上被调用)，我们得到:

select key, value
  from datatable, json_tree( datatable.data, '$.ALL' )
 where type = 'object'
   and json_extract( value, '$.pod' ) = 'fb' ;

返回:

2|{"comments":"you like everything","data":["facebook"],"pod":"fb"}
1|{"comments":"inserted by TripeHound","data":["facebook"],"pod":"fb"}

如果需要，我们可以使用 json_extract()分解返回的对象:

.mode column
.headers on
.width 30 15 5
select json_extract( value, '$.comments' ) as Comments,
       json_extract( value, '$.data' ) as Data,
       json_extract( value, '$.pod' ) as POD
  from datatable, json_tree( datatable.data, '$.ALL' )
 where type = 'object'
   and json_extract( value, '$.pod' ) = 'fb' ;

Comments                        Data             POD
------------------------------  ---------------  -----
you like everything             ["facebook"]     fb
inserted by TripeHound          ["facebook"]     fb

备注 :如果您的结构包含其他不同格式的对象，仅选择 type = 'object' 可能是不够的。 :您可能需要设计一个更微妙的过滤过程。