Postgres jsonb object IN array of json objects

Question

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

Can you please clarify if you need a condition that will only return rows where both dates are present or if you want a solution where at least one of the dates is present — user330315
– user330315, Commented Jun 4, 2020 at 8:36
@a_horse_with_no_name clarification added.. I need to run an WHERE OR, so at least one of the dates is present. — wakakak
– wakakak, Commented Jun 4, 2020 at 23:16
@a_horse_with_no_name sorry, was away for a couple of days, and now back to this project.. What I do not like about my second query, is that if I have a few months (or even a year) worth of date.. it will become a long line of query.. even though I would be constructing the query programmatically, I can't help but wonder if there is a shorter way to do it.. — wakakak
– wakakak, Commented Jun 9, 2020 at 0:29

Barbaros Özhan · Accepted Answer · 2020-06-04 08:47:50Z

1

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

edited Jun 4, 2020 at 8:47

answered Jun 4, 2020 at 8:22

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

5 Comments

user330315 Over a year ago

I understood "to include both date" as both values need to be present in the list, not just one (which the IN would do).

Barbaros Özhan Over a year ago

well @a_horse_with_no_name , I understood using IN operator whether contains both or single literals in the list :) . Thanks for suggestion.

user330315 Over a year ago

Unrelated, but: (login->>'byDate')::jsonb can be simplified to login->'byDate' and I think two @> conditions with an OR would be more efficient that unnesting all elements with a cross join

Barbaros Özhan Over a year ago

thank you @a_horse_with_no_name , converted to login->'byDate' .

wakakak Over a year ago

@BarbarosÖzhan this is giving me the result that I need.. thanks

score 1 · Accepted Answer · 2020-06-05 05:38:53Z

1

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.

edited Jun 5, 2020 at 5:38

answered Jun 4, 2020 at 7:42

user330315

2 Comments

wakakak Over a year ago

I actually need an OR (at least one).. I am weighing whether to use the OR condition multiple times (like in my original post) or do the solution from Barbaros' answer.. thanks

user330315 Over a year ago

@GlenK: there is another solution using EXISTS that might be more efficient than using a CROSS JOIN and DISTINCT

Collectives™ on Stack Overflow

Postgres jsonb object IN array of json objects

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related