I have an PostgreSQL database with complex JSON structures and need to check if a imbedded value exists somewhere in an array.

The field looks something like:

I have an PostgreSQL database with complex JSON structures and need to check if a imbedded value exists somewhere in an array.

The field looks something like:

I want to check if Value_Name with Value exists in any of the elements in Inner array with an array of arbitrary length.

I think I should be able to do it with the @> operator but can't seem to get it working.

SELECT * 
FROM table 
WHERE field->'Outer'->'Inner' @> ARRAY['{"Value_Name":"Value"}']::JSONB

But this doesn't work. Can anyone suggest what I can do search the array?

I am using PostgreSQL 13.9.

• sql
• postgresql
• jsonb
• jsonpath
• postgresql-13

• Please review minimal reproducible example and update the post accordingly. Provide usable example data without the additional noise associated with embedded comments indicating elided elements. – JohnH Commented Jan 23 at 15:56
• select jsonb_path_exists(field, '$.Outer.Inner[*] ? (@.Value_Name == "Value")') – Mike Organek Commented Jan 23 at 18:11
• @MikeOrganek Using the function disables index use. The @@ operator can do that, without sacrificing the index support: where jb @@ 'exists($.Outer.Inner[*].Value_Name?(@=="Value"))' or just '$.Outer.Inner[*]."Value_Name"=="Value"' because missing and present but unequal both lead to a mismatch. – Zegarek Commented Jan 23 at 18:16

1 Answer 1

Your thinking isn't wrong, it could work the way you described as long as you make sure you construct the value on the right of @> correctly: right now you're trying to cast a text[] directly to jsonb, which won't work.

If you instead pass it to to_jsonb(), it will, but you won't get the result: your array is a text[], so that gets you a jsonb array of string scalars, which isn't what you want. You'd have to start off with an array of jsonb[] to make sure the values inside are proper JSON objects, then pass that through to_jsonb() to get an array of objects:
_{demo at db<>fiddle}

SELECT * 
FROM test 
WHERE jb->'Outer'->'Inner' @> to_jsonb(ARRAY['{"Value_Name":"Value"}'::jsonb])

Still, any of these would work as well:

select*from test
where jb['Outer']['Inner'] @> '[{"Value_Name":"Value"}]';

select*from test
where jb @> '{"Outer":{"Inner":[{"Value_Name":"Value"}]}}';

With a JSONPath @@ predicate check:

select*from test
where jb @@ '$.Outer.Inner[*]."Value_Name"=="Value"';

And you could even try to search for the key:value pair on any level inside the JSON structure using .** accessor:

select*from test
where jb @@ '$.**."Value_Name"=="Value"';