Iteration over json array elements in PostgreSQL

Question

I've got two JSON data row from the column in postgresql database and that looks like this.

{
  "details":[{"to":"0:00:00","from":"00:00:12"}]
}

{
  "details":[ 
             {"to":"13:01:11","from":"13:00:12"}, 
             {"to":"00:00:12","from":"13:02:11"}
            ]
}

I want to iterate over details and get only the "from" key values using a query in postgresql. I want it like

The data-type of the column is jsonb or json?

Grim
– Grim

2019-03-10 07:00:39 +00:00
Commented Mar 10, 2019 at 7:00 — Grim
– Grim, Commented Mar 10, 2019 at 7:00
yes, the datatype of a column is jsonb

rakesh
– rakesh

2019-03-10 07:02:28 +00:00
Commented Mar 10, 2019 at 7:02 — rakesh
– rakesh, Commented Mar 10, 2019 at 7:02

Kaushik Nayak · Accepted Answer · 2019-03-10 07:10:50Z

4

Use jsonb_array_elements

select j->>'from' as "from" from t 
cross join jsonb_array_elements(s->'details') as j;

Demo

answered Mar 10, 2019 at 7:10

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

2 Comments

rakesh Over a year ago

The above details are from two columns and I need to fetch all other columns like id using the key "from". from time range between 13:00:00 and 13:03:00.

Kaushik Nayak Over a year ago

@rakesh : This answers your original question, please consider asking a new one with the additional details

Collectives™ on Stack Overflow

Iteration over json array elements in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related