Primoz Primoz - 5 months ago 19
JSON Question

Convert array of records to JSON in Postgresql

I'm having problems with converting an array of records into JSON with Postgresql.

Version: psql (PostgreSQL) 9.5.3

Current query:

SELECT c.id, (select array(
select (cp.id,cp.position)
from contactposition cp
where cp.contact_id_id = c.id -- join on the two tables
)
) as contactpositions
from contacts c;


Contact from table
contacts
can have many positions assigned from
contactposition
table.

Result is something like this:

| id (integer) | contactpositions (record[]) |
|--------------|----------------------------------------------------------------------|
| 5 | {"(21171326,\"Software Developer\")","(21171325,Contractor)" (...)"} |


But I would like it to be something like this:

| id (integer) | contactpositions (record[]) |
|--------------|----------------------------------------------------------------------|
| 5 | [{"id": 21171326, "position": "Software Developer", "id": 21171325, "position": "Contractor", (...)] |


I am aware of several helper functions like
array_to_json
, but I just can't get it to work.

I've tried:

SELECT c.id, array_to_json(select array(
select (cp.id,cp.position)
from contactposition cp
where cp.contact_id_id = c.id
)
) as contactpositions
from contacts c;


But it throws:
ERROR: syntax error at or near "select"
, so obviously I'm not using it right.

I would appreciate any tips, thanks!

Answer

Use jsonb_build_object() and jsonb_agg():

select c.id, jsonb_agg(jsonb_build_object('id', cp.id, 'position', cp.position))
from contacts c
join contactposition cp on c.id = cp.contact_id
group by 1;
Comments