Search code examples
postgresqljsonb

Postgres JSONB multiple attribute matches

Say I have a JSONB object like this:

{"First":"Joe", "Last":"Smith", "Age": "29", "cat":"meow"}

I want to be able to find this JSONB object if I search just:

{"First":"Joe", "Age":"29"}

I tried this with a single attribute and it worked:

SELECT * FROM mytable WHERE name @> lower('{"First": "Joe"}')::jsonb

I tried this with two attributes and it did not work:

SELECT * FROM mytable WHERE name @> lower('{"First": "Joe", "Last":"Smith"}')::jsonb

What am I missing? I figured based on the documentation this should work

Solution

  • Remove the lower(), @> is case-sensitive.

    SELECT * FROM mytable WHERE name @> '{"First": "Joe", "Last":"Smith"}'::jsonb

    If you want to make it a case-insensitive search, use lower() on the textual value of the JSON and put all attributes to match in lowercase (or may be apply lower() too )

    SELECT * FROM mytable WHERE lower(name::text)::jsonb 
        @> '{"first": "joe", "last":"smith"}'::jsonb

    DEMO