Tauras Tauras - 2 months ago 11
JSON Question

PostgreSQL json and array processing

I need to output json out from the query.

Input data:

Documents:
==========
id | name | team
------------------
1 | doc1 | {"authors": [1, 2, 3], "editors": [3, 4, 5]}

Persons:
========
id | name |
--------------
1 | Person1 |
2 | Person2 |
3 | Person3 |
4 | Person4 |
5 | Person5 |


Query:

select d.id, d.name,
(select jsonb_build_object(composed)
from
(
select teamGrp.key,
(
select json_build_array(persAgg) from
(
select
(
select jsonb_agg(pers) from
(
select person.id, person.name
from
persons
where (persList.value)::int=person.id
) pers
)
from
json_array_elements_text(teamGrp.value::json) persList
) persAgg
)
from
jsonb_each_text(d.team) teamGrp
) teamed
) as teams
from
documents d;


and i expect the following output:

{"id": 1, "name": "doc1", "teams":
{"authors": [{"id": 1, "name": "Person1"}, {"id": 2, "name": "Person2"}, {"id": 3, "name": "Person3"}],
"editors": [{"id": 3, "name": "Person3"}, {"id": 5, "name": "Person5"}, {"id": 5, "name": "Person5"}]}


But received an error:

ERROR: more than one row returned by a subquery used as an expression

Where is the problem and how to fix it?

PostgreSQL 9.5

Answer

I think the following (super complicated query) should to it:

SELECT
    json_build_object(
        'id',id,
        'name',name,
        'teams',(
            SELECT json_object_agg(team_name,
                       (SELECT
                            json_agg(json_build_object('id',value,'name',Persons.name))
                        FROM json_array_elements(team_members)
                             INNER JOIN Persons ON (value#>>'{}')::integer=Persons.id
                       )
                   )
            FROM json_each(team) t(team_name,team_members)
        )
    )
FROM Documents;

I am using subqueries where I run json aggregates.