Aggregate JSON arrays with column values as keys

Question

I've got a scenario where I'm trying to aggregate data and insert that aggregated data into another table, all from inside of a function. The data is being inserted into the other table as arrays and JSON. I've been able to aggregate into arrays perfectly fine, but I'm running into some trouble trying to aggregate the data into JSON the way I want.

Basically here is a sample of the data I'm aggregating:

 id_1 | id_2 | cat_ids_array
------+------+---------------
  201 | 4232 | {9,10,11,13}
  201 | 4236 | {11}
  201 | 4249 | {12}
  201 | 4251 | {9,10}
  202 | 4245 | {11}
  202 | 4249 | {12}
  202 | 4251 | {9,10}
  202 | 4259 | {9}
  203 | 4232 | {9,10,11,13}
  203 | 4236 | {11}
  203 | 4249 | {12}
  203 | 4251 | {9,10}
  203 | 4377 | {14}
  204 | 4232 | {15,108}
  204 | 4236 | {15}
  205 | 4232 | {17,109}
  205 | 4245 | {17}
  205 | 4377 | {18}
  206 | 4253 | {20}

When I use json_agg() to aggregate the id_2 and cat_ids_array into a JSON string here is what I get:

 id_1 |             json_agg             
------+----------------------------------
  201 | [{"f1":4232,"f2":[9,10,11,13]}, +
      |  {"f1":4236,"f2":[11]},         +
      |  {"f1":4249,"f2":[12]},         +
      |  {"f1":4251,"f2":[9,10]}]
  202 | [{"f1":4245,"f2":[11]},         +
      |  {"f1":4249,"f2":[12]},         +
      |  {"f1":4251,"f2":[9,10]},       +
      |  {"f1":4259,"f2":[9]}]
  203 | [{"f1":4232,"f2":[9,10,11,13]}, +
      |  {"f1":4236,"f2":[11]},         +
      |  {"f1":4249,"f2":[12]},         +
      |  {"f1":4251,"f2":[9,10]}        +
      |  {"f1":4377,"f2":[14]}]
  204 | [{"f1":4232,"f2":[15,108]},     +
      |  {"f1":4236,"f2":[15]}]
  205 | [{"f1":4232,"f2":[17,109]},     +
      |  {"f1":4245,"f2":[17]},         +
      |  {"f1":4377,"f2":[18]}]
  206 | [{"f1":4253,"f2":[20]}]

Here is what I'm trying to get:

 id_1 | json_agg
------+-------------------------------------------------------------
  201 | [{"4232":[9,10,11,13],"4236":[11],"4249":[12],"4251":[9,10]}]
  202 | [{"4245":[11],"4249":[12],"4251":[9,10],"4259":[9]}]
  203 | [{"4232":[9,10,11,13],"4236":[11],"4249":[12],"4251":[9,10],"4377":[14]}]
  204 | [{"4232":[15,108],"4236":[15]}]
  205 | [{"4232":[17,109],"4245":[17],"4377":[18]}]
  206 | [{"4253":[20]}]

I'm thinking that I will have to do some kind of string concatenation, but I'm not entirely sure the best way to go about this. As stated before, I'm doing this from inside of a function, so I've got some flexibility in what I can do since I'm not limited to just SELECT syntax magic.

Also pertinent, I'm running PostgreSQL 9.3.4 and cannot upgrade to 9.4 in the near future.

Erwin Brandstetter · Accepted Answer · 2022-05-05T02:49:56.380

It's a pity you cannot upgrade, Postgres 9.4 has jsonb and much added functionality for JSON. In particular json_build_object() would be perfect for you:

Return multiple columns of the same row as JSON array of objects

Almost, but not quite

While stuck with Postgres 9.3, you can get help from hstore to construct an hstore value with id_2 as key and cat_ids_array as value:

hstore(id_2::text, cat_ids_array::text)

Or:

hstore(id_2::text, array_to_json(cat_ids_array)::text)

Then:

json_agg(hstore(id_2::text, array_to_json(cat_ids_array)::text))

But the array is not recognized as array. Once cast to hstore, it's a text string for Postgres. There is hstore_to_json_loose(), but it only identifies boolean and numerical types.

Solution

So I ended up with string manipulation like you predicted. there are various ways to construct the json string. Each is more or less fast / elegant:

format('{"%s":[%s]}', id_2::text, translate(cat_ids_array::text, '{}',''))::json
format('{"%s":%s}', id_2::text, to_json(cat_ids_array))::json
replace(replace(to_json((id_2, cat_ids_array))::text, 'f1":',''),',"f2', '')::json

I picked the second variant, seems to be the most reliable and works for other array types than the simple int[] as well, which might need escaping:

SELECT id_1
     , json_agg(format('{"%s":%s}', id_2::text, to_json(cat_ids_array))::json)
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

Result as desired.

SQL Fiddle demonstrating all.

Aggregate JSON arrays with column values as keys

1 Answers1

Almost, but not quite

Solution