在sqlite中使用json_extract从父对象和子对象中提取数据

Question

我开始探索sqlite的JSON1库，到目前为止，我已经成功地创建了基本查询。我现在希望创建一个更复杂的查询，从多个级别提取数据。

这是我开始使用的示例JSON对象(大多数数据非常相似)。

{
"height": 140.0,
"id": "cp",
"label": {
"bind": "cp_label"
},
"type": "color_picker",
"user_data": {
"my_property": 2
},
"uuid": "948cb959-74df-4af8-9e9c-c3cb53ac9915",
"value": {
"bind": "cp_color"
},
"width": 200.0
}

这个json对象隐藏在json结构的七层深处，我使用如下sql语句从更大的json结构中提取它:

SELECT value FROM forms, json_tree(forms.formJSON, '$.root') 
WHERE type = 'object'
AND json_extract(value, '$.id') = @sControlID
// In this example, @sControlID is a variable that represents the `id` value we're looking for, which is 'cp'

但是我真正需要从这个对象中提取的是以下内容:

• 键type("color_picker">
• 键bind("cp_color"one_answers";cp_label">
• 键value和label(在本例中其值为{"bind":"<string>"})

对于最后一项，键名(在本例中为value和label)可以是任意数量的关键字，但无论关键字是什么，值都将是形式为{"bind":"<some_string>"}的对象。此外，可能有多个键与bind对象相关联，我需要返回所有这些键。对于前两项，关键字始终是type和bind。

对于上面的json示例，我理想地希望检索两行:

type          key     value
color_picker  value   cp_color
color_picker  label   cp_label

当我使用json_extract方法时，我最终从json_tree表中检索对象{"bind":"cp_color"}，但我还需要从父对象中检索数据。我觉得我需要做一些结合，但我的尝试到目前为止都没有成功。有什么想法吗?

注意:如果{"bind":"<string>"}对象不作为父对象的子对象存在，我不希望返回任何行。

Answer 1

好吧，我走上了正确的道路，并最终找到了答案。我为我正在寻找的每个项目创建了一个单独的查询，然后INNER JOIN从每个查询中提取所有json_tree表，使所有必需的字段可用。然后我json_extract从我需要数据的每个json字段中获取所需的数据。最后，它给了我想要的东西，尽管我相信它可以写得更有效率。

对于任何感兴趣的人来说，这是最终查询的结果:

SELECT IFNULL(json_extract(parent.value, '$.type'), '_window_'), child.key, json_extract(child.value, '$.bind') FROM (SELECT json_tree.* FROM nui_forms, json_tree(nui_forms.formJSON, '$') WHERE type = 'object' AND json_extract(nui_forms.formJSON, '$.id') = @sWindowID) parent INNER JOIN (SELECT json_tree.* FROM nui_forms, json_tree(nui_forms.formJSON, '$') WHERE type = 'object' AND json_extract(value, '$.bind') != 'NULL' AND json_extract(nui_forms.formJSON, '$.id') = @sWindowID) child ON child.parent = parent.id;

如果您有任何关于降低其复杂性的建议，请随时发表评论!