CODEWITHSUNDEEP
postgresqljsonb
DoIt
DoIt

Reputation: 3438

Using IN statement with jsonb type in postgres

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 ?

Upvotes: 0

Views: 149

Answers (1)

jjanes
jjanes

Reputation: 44237

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') );

Upvotes: 1

Related Questions