How To Query an array of JSONB

Question

I have table (orders) with jsonb[] column named steps in Postgres db.

I need create SQL query to select records where Step1 and Step2 and Step3 has success status

[
 {
  "step_name"=>"Step1",
  "status"=>"success",
  "timestamp"=>1636120240
  },
 {
  "step_name"=>"Step2",
  "status"=>"success",
  "timestamp"=>1636120275
 },
 {
  "step_name"=>"Step3",
  "status"=>"success",
  "timestamp"=>1636120279
 },
 {
  "step_name"=>"Step4", 
  "timestamp"=>1636120236
  "status"=>"success"
  }
]

table structure id | name | steps (jsonb)

Which Postgres version are you using?

user330315
– user330315

2021-11-18 10:40:18 +00:00
Commented Nov 18, 2021 at 10:40 — user330315
– user330315, Commented Nov 18, 2021 at 10:40
@a_horse_with_no_name 13.2

Timothy94
– Timothy94

2021-11-18 10:52:48 +00:00
Commented Nov 18, 2021 at 10:52 — Timothy94
– Timothy94, Commented Nov 18, 2021 at 10:52

Stefanov.sm · Accepted Answer · 2021-11-18 13:18:38Z

1

'Normalize' steps into a list of JSON items and check whether every one of them has "status":"success". BTW your example is not valid JSON. All => need to be replaced with : and a comma is missing.

select id, name from orders
where
(
 select bool_and(j->>'status' = 'success') 
 from jsonb_array_elements(steps) j
 where j->>'step_name' in ('Step1','Step2','Step3') -- if not all steps but only these are needed
);

edited Nov 18, 2021 at 13:18

answered Nov 18, 2021 at 13:00

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

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

2 Comments

Timothy94 Over a year ago

Can we use this query with CASE...THEN... END clause ? For ex. if Step1','Step2','Step3' is success order has "Devilered" status

Stefanov.sm Over a year ago

Yes, for sure. There are several options. I am trying however to make queries as declarative as possible and avoid hard-coding of details. But this is a matter of personal taste actually.

Pooya · Accepted Answer · 2021-11-18 12:46:28Z

0

You can use JSON value contain operation for check condition exist or not

Demo

select
  *
from
  test
where
  steps @> '[{"step_name":"Step1","status":"success"},{"step_name":"Step2","status":"success"},{"step_name":"Step3","status":"success"}]'

answered Nov 18, 2021 at 12:46

Pooya

3,2032 gold badges16 silver badges21 bronze badges

1 Comment

Stefanov.sm Over a year ago

What if the number of steps is unknown upfront?

Collectives™ on Stack Overflow

How To Query an array of JSONB

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related