PostgreSQL - Create an array of jsonb using group by clause-CodePudding

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.

CodePudding user response：

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