PostgreSQL json and array processing

Question

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

redneb · Accepted Answer · 2016-09-13 13:44:09Z

1

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.

edited Sep 13, 2016 at 13:44

answered Sep 13, 2016 at 12:57

redneb

24.3k7 gold badges46 silver badges55 bronze badges

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

2 Comments

Tauras Over a year ago

Thanks. But in teams there can be more objects, not only authors and editors and they vary. For me it is a complication to combine key and values as array of objects.

redneb Over a year ago

@Tauras I updated my answer, it can now deal with a variable number of teams. In fact that made the query slightly simpler.

Collectives™ on Stack Overflow

PostgreSQL json and array processing

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related