Read postgresql column with Json data

Question

i'm trying to extract information from a column containing json data.

Here is an example:

statushistory   idorder
[{"timestamp":"2016-10-10T14:42:23.386Z","status":"unassigned"},{"timestamp":"2016-10-10T07:21:08.634Z","status":"baseline"},{"timestamp":"2016-10-11T08:36:55.882Z","status":"inTransit"},{"timestamp":"2016-10-10T11:03:36.491Z","status":"toReschedule"},{"timestamp":"2016-10-11T07:30:56.897Z","status":"baseline"},{"timestamp":"2016-10-10T08:07:36.500Z","status":"inTransit"},{"timestamp":"2016-10-10T11:11:20.489Z","status":"committed"},{"timestamp":"2016-10-10T05:59:45.761Z","status":"unassigned"},{"timestamp":"2016-10-11T12:15:25.975Z","status":"fulfilled"},{"timestamp":"2016-10-11T12:16:25.766Z","status":"fulfilled"},{"timestamp":"2016-10-10T11:02:36.442Z","status":"toReschedule"}]   281474981556785
[{"timestamp":"2016-10-11T12:40:29.373Z","status":"toReschedule"},{"timestamp":"2016-10-11T12:41:29.710Z","status":"toReschedule"},{"timestamp":"2016-10-11T07:30:57.231Z","status":"baseline"},{"timestamp":"2016-10-11T14:26:45.995Z","status":"rescheduled"},{"timestamp":"2016-10-11T08:36:59.080Z","status":"inTransit"},{"timestamp":"2016-10-10T14:42:23.387Z","status":"unassigned"}]   281474983220313
    [{"timestamp":"2016-10-11T14:03:28.137Z","status":"baseline"},{"timestamp":"2016-10-11T19:37:44.172Z","status":"rescheduled"},{"timestamp":"2016-10-11T18:44:33.473Z","status":"toReschedule"},{"timestamp":"2016-10-10T14:42:23.385Z","status":"unassigned"},{"timestamp":"2016-10-11T18:45:35.284Z","status":"toReschedule"},{"timestamp":"2016-10-11T14:39:34.867Z","status":"inTransit"}]   281474983332443
    [{"timestamp":"2016-10-11T08:21:59.122Z","status":"rescheduled"}]   281474983118369
    [{"timestamp":"2016-10-11T10:40:25.666Z","status":"fulfilled"},{"timestamp":"2016-10-10T14:42:23.386Z","status":"unassigned"},{"timestamp":"2016-10-11T10:39:25.888Z","status":"fulfilled"},{"timestamp":"2016-10-11T07:30:56.971Z","status":"baseline"},{"timestamp":"2016-10-11T08:36:56.033Z","status":"inTransit"}] 281474982795357
    [{"timestamp":"2016-10-10T14:42:23.386Z","status":"unassigned"},{"timestamp":"2016-10-11T14:03:20.803Z","status":"baseline"},{"timestamp":"2016-10-11T16:19:27.735Z","status":"fulfilled"},{"timestamp":"2016-10-11T14:39:30.396Z","status":"inTransit"},{"timestamp":"2016-10-11T16:18:26.816Z","status":"fulfilled"}] 281474981917259
    [{"timestamp":"2016-10-11T09:25:26.100Z","status":"fulfilled"},{"timestamp":"2016-10-10T14:42:23.386Z","status":"unassigned"},{"timestamp":"2016-10-11T08:36:56.132Z","status":"inTransit"},{"timestamp":"2016-10-11T07:30:56.979Z","status":"baseline"},{"timestamp":"2016-10-11T09:26:26.241Z","status":"fulfilled"}] 281474981917255

I'm trying to obtain for each row the values for "status" and "timestamp". I cannot understand how can i isolate those two infos for each row.

I've tried with:

select 
 statushistory::json -> 'status',
 statushistory::json -> 'timestamp',
 idorder
from json_data;

But it gives me only a completely blank column.

I'm trying to obtain something like this:

status       timestamp idorder 
inStock      xxx       id1
failed       yyy       id1
toReschedule zzz       id2

Am i doing something wrong?

Oto Shavadze · Accepted Answer · 2018-09-21 14:54:17Z

1

Instead of calling jsonb_array_elements_text() 2 times, you can do it with lateral join and jsonb_array_elements()

select e.v->>'status' , e.v->>'timestamp', json_data.idorder
from json_data
join lateral jsonb_array_elements(statushistory) e(v)
on true

answered Sep 21, 2018 at 14:54

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

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

Comments

xCloudx8 · Accepted Answer · 2018-09-21 14:44:57Z

Ok, i found it! Here's the code:

select 
    jsonb_array_elements_text(statushistory::jsonb)::jsonb -> 'status',
    jsonb_array_elements_text(statushistory::jsonb)::jsonb -> 'timestamp',
    idorder
from json_data;

Here's the output:

"unassigned"    "2016-10-10T14:42:23.386Z"  281474981556785
"baseline"  "2016-10-10T07:21:08.634Z"  281474981556785
"inTransit" "2016-10-11T08:36:55.882Z"  281474981556785
"toReschedule"  "2016-10-10T11:03:36.491Z"  281474981556785
"baseline"  "2016-10-11T07:30:56.897Z"  281474981556785
"inTransit" "2016-10-10T08:07:36.500Z"  281474981556785
"committed" "2016-10-10T11:11:20.489Z"  281474981556785
"unassigned"    "2016-10-10T05:59:45.761Z"  281474981556785
"fulfilled" "2016-10-11T12:15:25.975Z"  281474981556785
"fulfilled" "2016-10-11T12:16:25.766Z"  281474981556785

Thank you all.

Collectives™ on Stack Overflow

Read postgresql column with Json data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related