Convert array of records to JSON in Postgresql

Question

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!

Oops, updated it in the question. It is 9.5.3.

primoz
– primoz

2016-06-28 14:07:41 +00:00
Commented Jun 28, 2016 at 14:07 — primoz
– primoz, Commented Jun 28, 2016 at 14:07

klin · Accepted Answer · 2016-06-28 14:18:12Z

15

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;

edited Jun 28, 2016 at 14:18

answered Jun 28, 2016 at 14:12

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

primoz Over a year ago

Perfect! Thank you very much!

Collectives™ on Stack Overflow

Convert array of records to JSON in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related