Convert array of records to JSON in Postgresql

Primoz Published at Dev

primoz

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!

klin

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;

Collected from the Internet

Please contact [email protected] to delete if infringement.