Json array manipulation with postgres

Question

I am very new to postgres, I am trying to manipulate data present in two tables and insert the resultant into a new table.

The first table looks like:

create table table1
(
  column1 json,
  column2 json
)

Data in the first table is
column1:
{"source" : ["s1", "s2"], "channels":["c1", "c2"]}
column2:
{"c1" : ["k1", "k2"], "c2":["k3", "k4"]}

The second table looks very alike

create table table2
(
  column1 json,
  column2 json
)

Data in the second table is
column1:
{"source" : ["s2", "s3"], "channels":["c2", "c3"]}
column2:
{"c2" : ["k1", "k2", "k5"], "c3":["k6", "k7", k8]}

I want to combine the data of both table (1 and 2) into one into json with unique array values and put it into a third table

The third table has the same structure as that of table 1 and table 2 and should have data as below

Data in the third table should be :
column1:
{"source" : ["s1", "s2", "s3"], "channels":["c1", "c2", "c3"]}
column2:
{"c1":["k1", k2"], "c2":["k1", "k2", "k3", "k4", "k5"], "c3":["k6", "k7", "k8"]}

I tried many different types of query structure but somehow I am unable to accomplish the above task. One among them is,

SELECT array_cat(ARRAY(SELECT json_extract_path_text(a.column1, 'source')), ARRAY(SELECT json_extract_path_text(b.column1, 'source'))) AS txt_arr FROM   table1 a, table2 b;

Please dont mind the above query, its not even half-way correct.

since I am new to postgres, I would really appreciate any sort of help.

Thank you

What exactly are you trying to do? Please bear in mind that this is an RDBMS and not an NoSQL database (even though it does have excellent support for JSON) — e4c5
– e4c5, Commented Jun 15, 2016 at 14:34
Inserts to the tables are not very frequent. The logic is written inside a function which is invoked by a trigger upon insert into the table1 and table2. Also, the data is less comparatively as the inserts are not very frequent. — Sam
– Sam, Commented Jun 15, 2016 at 14:38
No I am not asking about your inserts. I am asking why you are structuring your data like this. — e4c5
– e4c5, Commented Jun 15, 2016 at 14:39
I have made few changes to the question, please have a look. Actually the no of channels and no of keys inside a channel can increase to a very large number. If I denormalize them and store them in a table then their will be a lot of repeating source and channels for a user. nevertheless, computation will be fast, however my data into the table is not very frequent so computation is not an priority. — Sam
– Sam, Commented Jun 15, 2016 at 14:52

Ezequiel Tolnay · Accepted Answer · 2016-06-16 07:49:13Z

2

If you must...

select (
  select json_object_agg(key, vals)
  from (
    select key, json_agg(value) vals
    from (
      select j.key, v.value
      from table1
      left join lateral json_each(column1) j on (true)
      left join lateral json_array_elements_text(j.value) v on (true)
      union
      select j.key, v.value
      from table2
      left join lateral json_each(column1) j on (true)
      left join lateral json_array_elements_text(j.value) v on (true)
      ) t1
    group by key
    ) t1
  ) column1,
  (
  select json_object_agg(key, vals)
  from (
    select key, json_agg(value) vals
    from (
      select j.key, v.value
      from table1
      left join lateral json_each(column2) j on (true)
      left join lateral json_array_elements_text(j.value) v on (true)
      union
      select j.key, v.value
      from table2
      left join lateral json_each(column2) j on (true)
      left join lateral json_array_elements_text(j.value) v on (true)
      ) t1
    group by key
    ) t1
  ) column2

I don't think it can be simplified, due the heterogeneity of the data.

edited Jun 16, 2016 at 7:49

answered Jun 16, 2016 at 3:41

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

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

2 Comments

Sam Over a year ago

Oh thank you so much for the awesome code. However I face an error while doing an union with the json datatypes. so I made a minor change in the code. I changed the json to text and again back to json while aggregating. select json_object_agg(key, vals) from (select key, json_agg(value::json) as vals from ( select j.key, v.value::text from table1 ........ and so on

Ezequiel Tolnay Over a year ago

@Sam I've fixed the code (I had tested it with jsonb, then replaced all jsonb with json without testing :-P ), try now

Collectives™ on Stack Overflow

Json array manipulation with postgres

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