2

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

ray_voelker
  • 495
  • 3
  • 12

1 Answers1

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'
  • 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! – ray_voelker Jan 18 '22 at 12:56