Блог

Я начинаю изучать библиотеку JSON1 для sqlite и до сих пор успешно справлялся с основными запросами, которые я создал. Теперь я хочу создать более сложный запрос, который извлекает данные с нескольких уровней.

Вот пример объекта 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, и я вытащил его из более крупной конструкции json, используя инструкцию sql, подобную этой:

 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» и «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, я в конечном итоге извлекаю объект {"bind":"cp_color"} из таблицы json_tree, но мне также нужно извлечь данные из родительского объекта. Я чувствую, что мне нужно создать какой-то союз, но мои попытки до сих пор не увенчались успехом. Есть какие-нибудь идеи?

Примечание: если {"bind":"<string>"} объект не существует как дочерний объект родительского объекта, я не хочу, чтобы возвращались какие-либо строки.

Что ж, я был на правильном пути и в конце концов понял это. Я создал отдельный запрос для каждого из элементов, которые я искал, затем INNER JOIN отредактировал все json_tree таблицы из каждого запроса, чтобы были доступны все необходимые поля. Затем я отредактировал json_extract необходимые данные из каждого поля json, из которого мне нужны были данные. В конце концов, это дало мне именно то, что я искал, хотя я уверен, что это можно было бы написать более эффективно.

Для всех, кто заинтересован, вот как выглядел окончательный запрос hte:

 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, '

Если у вас есть какие-либо советы по снижению его сложности, не стесняйтесь комментировать!