extract json array data from json column in postgres

Question

Can anyone help me how to extract the json array data which is stored in column "TimeCounterTotals" from this select query?? when I am passing hard coded data to json_to_recordset function, it works fine but how to do in select query?

select "CompanyId", json_to_recordset("TimeCounterTotals") as x("TimeTotal" decimal, "TimeCounterId" varchar)
from "TimeCalculationAndApprovals" where "TimeCounterTotals" is not null limit 1;

Adrian Klaver · Accepted Answer · 2020-07-03 19:55:30Z

2

Not exactly sure what you want, but as a start:

create table json_test(id integer, fld_json json);
insert into json_test values (1, '[{"a":1,"b":"foo"},{"a":2,"b":"bar"}]'::json), (2, '[{"a":3,"b":"test"},{"a": 4,"b":"test2"}]'::json); 

select id, a, b from json_test, json_to_recordset(json_test.fld_json) as x(a integer, b varchar);
 id | a |   b   
----+---+-------
  1 | 1 | foo
  1 | 2 | bar
  2 | 3 | test
  2 | 4 | test2

answered Jul 3, 2020 at 19:55

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

extract json array data from json column in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related