CODEWITHSUNDEEP
sqljsonpostgresqljsonb
wakakak
wakakak

Reputation: 842

Postgres jsonb object IN array of json objects

Say for example I have a table named user with login column with jsonb type and the record looks like this:

user
name  | login
-------------
test1 | {"byDate":[{"date":"2020-01-01"},{"date":"2020-01-02"},{"date":"2020-01-03"},]}
test2 | {"byDate":[{"date":"2020-01-02"},{"date":"2020-01-03"},{"date":"2020-01-04"},]}

How do I SELECT (with WHERE clause) to include both date '2020-01-01' and '2020-01-02'?

For one date, I can do:

SELECT * 
  FROM "user" 
 WHERE "login" @> '{"byDate":[{"date": "2020-01-01"}]}'

Can I use some sort of IN to select both '2020-01-01' and '2020-01-02'? Or do I just use OR, ex:

SELECT * 
  FROM "user" 
 WHERE "login" @> '{"byDate":[{"date": "2020-01-01"}]}' 
    OR "login" @> '{"byDate":[{"date": "2020-01-02"}]}'

I want the result to be an OR product, ex: I use '2020-01-01' OR '2020-01-03' both test1 AND test2 will show up in the result.

Any help would be much appreciated.

Thanks

Upvotes: 1

Views: 109

Answers (2)

user330315
user330315

Reputation:

You can use an EXISTS condition:

select *
from the_table
where exists (select *
              from jsonb_array_eleements(login -> 'byDate') as x(d)
              where x.d ->> 'date' in ('2020-01-01', '2020-01-02))

But your OR solution using @> will most probably be more efficient.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

One option would be using a query with jsonb_array_elements() function 

SELECT DISTINCT u.*
  FROM "user" u
 CROSS JOIN jsonb_array_elements(login->'byDate') j
 WHERE j->>'date' IN ('2020-01-01','2020-01-02')

which contains IN operator.

Demo

Upvotes: 1

Related Questions