CODEWITHSUNDEEP
postgresqljsonb
user10776719
user10776719

Reputation: 341

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

Upvotes: 1

Views: 227

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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

Upvotes: 1

Related Questions