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:
id | jdata |
---|---|
1 | { "Outer": { "Inner": [ { "Value_Name": "Value", "VARIOUS KEYS": "VARIOUS VALUES" }, { "Value_Name": "Value", "VARIOUS KEYS": "VARIOUS VALUES" } ], "VARIOUS KEYS": "VARIOUS VALUES" } } |
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:
id | jdata |
---|---|
1 | { "Outer": { "Inner": [ { "Value_Name": "Value", "VARIOUS KEYS": "VARIOUS VALUES" }, { "Value_Name": "Value", "VARIOUS KEYS": "VARIOUS VALUES" } ], "VARIOUS KEYS": "VARIOUS VALUES" } } |
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.
Share Improve this question edited Jan 23 at 17:53 Zegarek 26.7k5 gold badges24 silver badges30 bronze badges asked Jan 23 at 14:44 Lex ManLex Man 1791 silver badge13 bronze badges 3 |1 Answer
Reset to default 0Your 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"';
select jsonb_path_exists(field, '$.Outer.Inner[*] ? (@.Value_Name == "Value")')
– Mike Organek Commented Jan 23 at 18:11@@
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