Postgres JSONB multiple attribute matches

Question

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

What is not working? Did you receive an error? I tried your query in postgres 11 and it seems to work fine. — Jeremy
– Jeremy, Commented Jun 24, 2019 at 2:43

Kaushik Nayak · Accepted Answer · 2019-06-24 05:23:34Z

1

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

edited Jun 24, 2019 at 5:23

answered Jun 24, 2019 at 4:44

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres JSONB multiple attribute matches

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related