1

I have a list of JSON objects in a field in a postgres table and need to dynamically extract from this list.

The list can be varying in length depending on the record. Each JSON entry contains the same keys.

Here is an example:

`'root': [ {'field_1': 'ex_1', 'field_2': 'ex_2'},

{'field_1': 'ex_3', 'field_2': 'ex_4'} ]`

I would like to extract all of the fields from all JSON entries and enter into lists for each field and have them maintained in their own column in the table. i.e. field_1: [ex_1, ex_3], field_2: [ex_2, ex_4]

Any help would be appreciated.

Tried:

root ->> 'field_1' as field_1, etc returned null values for all entries

Brandon Chan
  • 11
  • 1
  • You cannot achieve what you are asking. The `root` key points to an array of json objects. That means you will need multiple rows to store the `field_1`, `field_2`, etc. values. If you are all right with creating multiple rows from a single table row, then look into `jsonb_array_elements()`. – Mike Organek Aug 25 '23 at 17:29
  • I see. I have utilized that function before, but was hoping there was another way to store in a single row. But thank you for the help! – Brandon Chan Aug 25 '23 at 18:06
  • Aside, using a normalized design would be so much simpler and better. – Stefanov.sm Aug 25 '23 at 19:13

1 Answers1

0

You have to first flatten the JSON field of your table and then re-aggregate into the desired structure. Well, not very simple indeed.

-- Sample data
create table t (id integer, root jsonb);
insert into t values 
(1, '[{"field_1":  "ex_1",  "field_2":  "ex_2"},  {"field_1":  "ex_3",  "field_2":  "ex_4"}]'),
(2, '[{"field_11": "ex_11", "field_12": "ex_12"}, {"field_11": "ex_13", "field_12": "ex_14"}]'),
(3, '[{"field_21": "ex_21", "field_22": "ex_22"}, {"field_21": "ex_23", "field_22": "ex_24"}]');

-- Flatten (example)
select id, k, v from t
cross join lateral jsonb_array_elements(root) j
cross join lateral jsonb_each(j) je(k, v);

-- Flatten and re-aggregate
with cte as
(
 select id, k, json_agg(v) newroot 
 from t
 cross join lateral jsonb_array_elements(root) j
 cross join lateral jsonb_each(j) je(k, v)
 group by id, k order by id, k
)
select id, jsonb_object_agg(k, newroot) newroot 
from cte group by id;
id newroot
1 {"field_1": ["ex_1", "ex_3"], "field_2": ["ex_2", "ex_4"]}
2 {"field_11": ["ex_11", "ex_13"], "field_12": ["ex_12", "ex_14"]}
3 {"field_21": ["ex_21", "ex_23"], "field_22": ["ex_22", "ex_24"]}

DB-fiddle

Alternatively, using a scalar subquery (and maybe more efficient):

select id, 
(
 select jsonb_object_agg(k, v)
 from (
  select k, jsonb_agg(v) v
  from jsonb_array_elements(root) j, lateral jsonb_each(j) je(k, v)
  group by k
 ) jarr
) new_root
from t;
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • Interesting - I will look into this approach more. I am not sure where the the attributes 'k' and 'v' are coming from though, so having trouble implementing this for my use case. Any more info would be appreciated! – Brandon Chan Aug 25 '23 at 21:34
  • @BrandonChan Attributes `k` and `v` come from function [`jsonb_each(j) as je(k,v)`](https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE) that is joined laterally with the list returned by `jsonb_array_elements`. Please note the use of [`lateral join`](https://stackoverflow.com/questions/28550679/what-is-the-difference-between-a-lateral-join-and-a-subquery-in-postgresql). Pls. note that `, lateral` is shorthand for `cross join lateral` – Stefanov.sm Aug 26 '23 at 05:45