CODEWITHSUNDEEP
arraysjsonpostgresql
Tauras
Tauras

Reputation: 58

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

Upvotes: 1

Views: 121

Answers (1)

redneb
redneb

Reputation: 23850

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.

Upvotes: 1

Related Questions