PostgreSQL - Create an array of jsonb using group by clause

Question

I have an SQL query that returns the data attached in the image. I would like to group the information and basically have just 2 rows (in this particular case). The row #1 would be: "evaluation_id": 1, "nombre": "Método: Jackson, Pollock & Ward" and "formulario" should be an array of json objects (in this case, records: 1,2,3 and 4). The row #2 would be: "evaluation_id": 2, "nombre": "Método: Medición Antropométrica Estándar" and "formulario" should be an array with only one json object (record #5).

evaluation_id	nombre	formulario
1	Jackson, Pollock & Ward	[{json1},{json2},{json3},{json4}]
2	Medición Antropométrica Estándar	[{json5}]

I have tried with functions like: array_to_json, json_object, json_aggr, json_array_elements but I couldn't make it work.

Pooya · Accepted Answer · 2021-11-03 05:53:17Z

2

Please check the output of this query your expected

-- if use json type
select evaluation_id, nombre, json_agg(formulario)
from your_table
group by 1, 2

-- if use jsonb type
select evaluation_id, nombre, jsonb_agg(formulario)
from your_table
group by 1, 2

answered Nov 3, 2021 at 5:53

Pooya

3,2032 gold badges16 silver badges21 bronze badges

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

3 Comments

Jose R. Chacón Over a year ago

Thank you @pooya, that works perfectly. What means the "group by 1, 2"?

Pooya Over a year ago

Your welcome, group 1, 2 means group evaluation_id, nombre. You can use column number instead column name in group by

Jose R. Chacón Over a year ago

Oh, I didn't know that. Thanks for all your help @Pooya, I really appreciate it.

Collectives™ on Stack Overflow

PostgreSQL - Create an array of jsonb using group by clause

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related