Postgres: row with comma separated value to array of json objects

Question

Using postgres 9.4.

Data:

+-----+------+
| id  | type |
+-----+------+
| 1   | A    |
+-----+------+
| 2,3 | A    |
+-----+------+
| 4   | B    |
+-----+------+

Desired output (JSON):

[
  [{"id": "1", "type": "A"}],
  [{"id": "2", "type": "A"},{"id": "3", "type": "A"}],
  [{"id": "4", "type": "B"}]
]

I've tried:

SELECT array_to_json(array_agg(c)) 
FROM 
(
  SELECT
    regexp_split_to_table(id, ',') AS id, 
    type 
  FROM my_table
) c;

which gets me to a simple array of json objects:

[
  {"id": "1", "type": "A"},
  {"id": "2", "type": "A"},
  {"id": "3", "type": "A"},
  {"id": "4", "type": "B"}]
]

How do I wrap each resultset (not each row) of the subquery in an array?

Gordon Linoff · Accepted Answer · 2019-04-23 11:39:35Z

2

I believe this does what you want:

with t as (
      select *
      from (values ('1', 'A'), ('2,3', 'A'), ('4', 'B')) v(ids, type)
     )
select json_agg(c)
from (select array_to_json(array_agg( json_build_object('id', c.id, 'type', c.type))) as c
      from (select ids, regexp_split_to_table(ids, ',') AS id, type 
            from t
           ) c
      group by ids
     ) i;

Here is a db<>fiddle.

answered Apr 23, 2019 at 11:39

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

siltalau Over a year ago

That is exactly right. In the CTE part I changed it to reference my actual table and it works. What does the v(ids, type) bit do?

siltalau Over a year ago

Oh yes, it names the columns of the subquery

user330315 · Accepted Answer · 2019-04-23 11:59:13Z

0

You can create the JSON value for a single row using:

select (select json_agg(to_json(t)) 
        from (
          select i.id, t.type 
          from unnest(string_to_array(t.ids, ',')
        ) as i(id)) t) json_id
from the_table t

you can wrap that into a derived table to aggregate everything to a single JSON value:

select json_agg(json_id)
from (
  select (select json_agg(to_json(t)) 
          from (select i.id, t.type from unnest(string_to_array(t.ids, ',')) as i(id)) t) json_id
  from the_table t
) x;

answered Apr 23, 2019 at 11:59

user330315

Collectives™ on Stack Overflow

Postgres: row with comma separated value to array of json objects

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related