最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

sql - How to check for values inside multiple array elements of a JSON string? - Stack Overflow

programmeradmin2浏览0评论

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
  • 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
Add a comment  | 

1 Answer 1

Reset to default 0

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"';
发布评论