PostgreSQL’s ROW_TO_JSON and JSON_AGG with JSON Columns

I’ve hit this a few times and had to remind myself how Postgres behaves when aggregating JSON columns or including them in ROW_TO_JSON.

The short answer is that Postgres does the right thing. When a JSON column is included in ROW_TO_JSON or JSON_AGG the resulting values are not nested, encoded JSON.

Here’s our sample table with some data:

CREATE TABLE test (group_by text, config JSON);

INSERT INTO test (group_by, config)
VALUES
    ('one', '{"one":1}'::JSON)
    , ('one', '{"one":1}'::JSON)
    , ('two', '{"two":2}'::JSON)
    , ('two', '{"two":2}'::JSON)
;

Let’s try ROW_TO_JSON first.

SELECT ROW_TO_JSON(test.*) example FROM test;

It’s easy to think that each row would be returned with the config columns still JSON encoded, meaning we’d have to do something like this:

let row = someSqlClient('SELECT ROW_TO_JSON(test.*) example FROM test');
let example = JSON.parse(row.example);
let config = JSON.parse(example.config);

That’s not the case. PostgreSQL does not nest encoded JSON, it instead puts everything into one level of JSON encoding.

                example                
---------------------------------------
 {"group_by":"one","config":{"one":1}}
 {"group_by":"one","config":{"one":1}}
 {"group_by":"two","config":{"two":2}}
 {"group_by":"two","config":{"two":2}}

The same is true for JSON_AGG.

SELECT group_by, JSON_AGG(config) example FROM test GROUP BY group_by

example comes out as a JSON array of objects.

 group_by |        example         
----------+------------------------
 two      | [{"two":2}, {"two":2}]
 one      | [{"one":1}, {"one":1}]

Share this: