Postgres: Nested Aggregate JSON

Question

The table I'm querying looks like this:

namespace | key   | value
---------------------------
foo       | bar   | baz
foo       | alpha | beta
gamma     | delta | epsilon

And I'd like to pull it out of the database like this:

{
    "foo": {
        "bar": "baz",
        "alpha": "beta"
    },
    "gamma": {
        "delta": "epsilon"
    }
}

Playing around with json_object_agg isn't really getting me past the first level, since you're not allowed to nest aggregate functions. But as far as I can see, I need a GROUP BY within a GROUP BY, but I'm not sure if that's possible. Perhaps the solution has to do with WINDOWs?

Clodoaldo Neto · Accepted Answer · 2015-11-05 11:36:23Z

3

with t (namespace, key, value) as (
    values
    ('foo','bar','baz'),('foo','alpha','beta'),('gamma','delta','epsilon')
), s as (
    select namespace, json_object_agg(key, value) as joa
    from t
    group by namespace
)
select json_object_agg(namespace, joa)
from s
;
                                  json_object_agg                                   
------------------------------------------------------------------------------------
 { "foo" : { "bar" : "baz", "alpha" : "beta" }, "gamma" : { "delta" : "epsilon" } }

As a CTE is an optimization barrier this version might be faster:

with t (namespace, key, value) as (
    values
    ('foo','bar','baz'),('foo','alpha','beta'),('gamma','delta','epsilon')
)
select json_object_agg(namespace, joa)
from (
    select namespace, json_object_agg(key, value) as joa
    from t
    group by namespace
) s

edited Nov 5, 2015 at 11:36

answered Nov 4, 2015 at 19:36

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Collectives™ on Stack Overflow

Postgres: Nested Aggregate JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related