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..

user15398259 · Accepted Answer · 2022-01-18 02:49:47Z

3

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'

answered Jan 18, 2022 at 2:49

user15398259

Sign up to request clarification or add additional context in comments.

1 Comment

ray_voelker Over a year ago

Thank you so much for that! That's interesting about being able to build the path with string concatenation And thank you for the suggestions on how the syntax for this works. That's much more clear!

Collectives™ on Stack Overflow

extract value from JSON object using SQLite and the json_tree function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related