Using json_extract in sqlite to pull data from parent and child objects

Question

I'm starting to explore the JSON1 library for sqlite and have been so far successful in the basic queries I've created. I'm now looking to create a more complicated query that pulls data from multiple levels.

Here's the example JSON object I'm starting with (and most of the data is very similar).

{
  "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
}

This json object is buried about seven levels deep in a json structure and I pulled it from the larger json construct using an sql statement like this:

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'

But what I really need to pull from this object are the following:

the value from key type ("color_picker" in this example)
the values from keys bind ("cp_color" and "cp_label" in this example)
the keys value and label (which have values of {"bind":"<string>"} in this example)

For that last item, the key name (value and label in this case) can be any number of keywords, but no matter the keyword, the value will be an object of the form {"bind":"<some_string>"}. Also, there could be multiple keys that have a bind object associated with them, and I'd need to return all of them. For the first two items, the keywords will always be type and bind.

With the json example above, I'd ideally like to retrieve two rows:

type          key     value
color_picker  value   cp_color
color_picker  label   cp_label

When I use json_extract methods, I end up retrieving the object {"bind":"cp_color"} from the json_tree table, but I also need to retrieve the data from the parent object. I feel like I need to do some kind of union, but my attempts have so far been unsuccessful. Any ideas here?

Note: if the {"bind":"<string>"} object doesn't exist as a child of the parent object, I don't want any rows returned.

score 0 · Answer 1 · answered Oct 07 '21 at 13:30

Well, I was on the right track and eventually figured out it. I created a separate query for each of the items I was looking for, then INNER JOINed all the json_tree tables from each of the queries to have all the required fields available. Then I json_extracted the required data from each of the json fields I needed data from. In the end, it gave me exactly what I was looking for, though I'm sure it could be written more efficiently.

For anyone interested, this is what hte final query ended up looking like:

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;

If you have any tips on reducing its complexity, feel free to comment!

Using json_extract in sqlite to pull data from parent and child objects

1 Answers1