Using IN statement with jsonb type in postgres

Question

I am trying to write a postgres sql query to select jsonb fields from my table and wondering if I can use IN statement with @> jsonb operator

The query I have is

SELECT data FROM catalog WHERE data @> '{"name":{"firstname":"myname"}}'

Above works fine with one value in WHERE condition, is it possible that I could use mutliple json in WHERE condition like along with '{"name":{"firstname":"myname"}}', I also want return records for '{"name":{"firstname":"yourname"}}'

I can do something like below

 Select * 
    FROM catalog
    WHERE data ->'name' ->> 'firstname'  IN ('myname','yourname')

Whats the best way to do it ?

Have you actually tried it, or what have you tried, and what were those results? Please provide sample test data, as text, and expected results. — Belayer
– Belayer, Commented Sep 13, 2019 at 16:25
@Belayer dbfiddle.uk/… I am trying find out what the best approach for querying with multiple conditions in WHERE clause — DoIt
– DoIt, Commented Sep 13, 2019 at 16:39

jjanes · Accepted Answer · 2019-09-14 16:09:21Z

1

Starting in the soon to be released v12, you can use JSONPATH to do that.

SELECT data FROM catalog WHERE data @@ '$.name.firstname=="myname" || $.name.firstname=="yourname"';

There may be a better to way to write that JSONPATH without the repetition, I'm not an expert there.

Your other choices are the IN you already shown, and multiple @> connected by OR. The different operations are supported by different indexes. If you care about performance, they the "best" way to do it depends on what indexes you already have, or are willing to build, and just how you prefer to write your queries. (Indeed I'd argue the "best" way is not to use JSON in the first place). To use the IN list, you would need an expressional index like:

create index on catalog ((data ->'name' ->> 'firstname') );

answered Sep 14, 2019 at 16:09

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Using IN statement with jsonb type in postgres

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