extract value from JSON object using SQLite and the json_tree function

Question

I have a table (named, patrons) that contains a column (named, json_patron_varfields) of JSON data--an array of objects that looks something like this:

[
    {
    "display_order": 1,
    "field_content": "example 1",
    "name": "Note",
    "occ_num": 0,
    "varfield_type_code": "x"
    },
    {
    "display_order": 2,
    "field_content": "example 2",
    "name": "Note",
    "occ_num": 1,
    "varfield_type_code": "x"
    },
    {
    "display_order": 3,
    "field_content": "some field we do not want",
    "occ_num": 0,
    "varfield_type_code": "z"
    }
]

What I'm trying to do is to target the objects that contain the key named varfield_type_code and the value of x which I've been able to do with the following query:

SELECT
patrons.patron_record_id,
json_extract(patrons.json_patron_varfields, json_tree.path)
FROM
patrons,
json_tree(patrons.json_patron_varfields)
WHERE
json_tree.key = 'varfield_type_code'
AND json_tree.value = 'x'

My Question is... how do I extract (or even possibly filter on) the values of the field_content keys from the objects I'm extracting?

I'm struggling with the syntax of how to do that... I was thinking it could be as simple as using json_extract(patrons.json_patron_varfields, json_tree.path."field_content") but that doesn't appear to be correct..

Answer 1

You can concat to build the string

json_tree.path || '.field_content'

With the structure you've given - you can also use json_each() instead of json_tree() which may simplify things.

extract:

SELECT
patrons.patron_record_id,
json_extract(value, '$.field_content')
FROM
patrons, 
json_each(patrons.json_patron_varfields)
WHERE json_extract(value, '$.varfield_type_code') = 'x'

filter:

SELECT
patrons.patron_record_id,
value
FROM
patrons, 
json_each(patrons.json_patron_varfields)
WHERE json_extract(value, '$.varfield_type_code') = 'x'
AND   json_extract(value, '$.field_content') = 'example 2'