时间戳

首页 > 解决方案 > 在 sqlite 中使用 json_extract 从父对象和子对象中提取数据

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

问题描述

我开始探索用于 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'

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

对于最后一项，键名（value在label这种情况下）可以是任意数量的关键字，但无论关键字是什么，值都是 form 的对象{"bind":"<some_string>"}。此外，可能有多个键bind与它们关联的对象，我需要返回所有它们。对于前两项，关键字将始终是typeand bind。

对于上面的 json 示例，理想情况下，我希望检索两行：

type          key     value
color_picker  value   cp_color
color_picker  label   cp_label

当我使用 json_extract 方法时，我最终会{"bind":"cp_color"}从 json_tree 表中检索对象，但我还需要从父对象中检索数据。我觉得我需要做某种联合，但迄今为止我的尝试都没有成功。这里有什么想法吗？

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

标签： jsonsqlitesqlite-json1

解决方案

好吧，我走在了正确的轨道上，最终想通了。我为我要查找的每个项目创建了一个单独的查询，然后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;

如果您有任何降低其复杂性的技巧，请随时发表评论！

推荐阅读