Re: JSON_AGG produces extra square brakets - Mailing list pgsql-general

This is a small testcase that reproduces the problem on my machine.

createdb --username=myuser --owner=myuser --encoding=UTF8 testcase


CREATE TABLE thing_template (
    id   serial   PRIMARY KEY
);
INSERT INTO thing_template VALUES ( 1 );


CREATE TABLE thing (
    id            serial    PRIMARY KEY,
    template_id   integer   REFERENCES thing_template   NOT NULL
);
INSERT INTO thing VALUES ( 1, 1 );
INSERT INTO thing VALUES ( 2, 1 );


CREATE TABLE tag (
    id     serial   PRIMARY KEY,
    name   text
);
INSERT INTO tag VALUES ( 1, 'tag 1' );
INSERT INTO tag VALUES ( 2, 'tag 2' );


CREATE TABLE thing_tag (
    thing_id   integer   REFERENCES thing   NOT NULL,
    tag_id     integer   REFERENCES tag     NOT NULL,
    PRIMARY KEY ( thing_id, tag_id )
);
INSERT INTO thing_tag VALUES ( 1, 1 );
INSERT INTO thing_tag VALUES ( 1, 2 );
INSERT INTO thing_tag VALUES ( 2, 1 );
INSERT INTO thing_tag VALUES ( 2, 2 );


CREATE TABLE summary_status (
    id         serial    PRIMARY KEY,
    severity   integer
);
INSERT INTO summary_status VALUES ( 1, 10 );
INSERT INTO summary_status VALUES ( 2, 20 );


CREATE TABLE thing_state (
    thing_template_id   integer   REFERENCES thing_template   NOT NULL,
    summary_status_id   integer   REFERENCES summary_status   NOT NULL,
    image_url           text,
    PRIMARY KEY ( thing_template_id, summary_status_id )
);
INSERT INTO thing_state VALUES ( 1, 1, 'img1.jpg' );
INSERT INTO thing_state VALUES ( 1, 2, 'img2.jpg' );



==================== QUERY  ====================

SELECT
    thing.id,
    tags,
    xtst.states
FROM
    thing,
    (SELECT thing_tag.thing_id AS thid, JSON_AGG( tag.name ) AS "tags" FROM thing_tag, tag WHERE (thing_tag.tag_id = tag.id) GROUP BY thing_tag.thing_id) xtg,
    (SELECT thing_state.thing_template_id, JSON_AGG( ROW_TO_JSON( (SELECT q FROM (SELECT thing_state.image_url, summary_status.severity) q) ) ) AS states FROM thing_state, summary_status WHERE (thing_state.summary_status_id = summary_status.id) GROUP BY thing_state.thing_template_id) xtst
WHERE
    (xtg.thid = thing.id) AND
    (xtst.thing_template_id = thing.template_id) AND
    (thing.id IN (1, 2));



====================  RESULT  ====================

 id |        tags        |                                      states                                      
----+--------------------+-----------------------------------------------------------------------------------
  1 | ["tag 1", "tag 2"] | [{"image_url":"img1.jpg","severity":10}, {"image_url":"img2.jpg","severity":20}]
  2 | ["tag 1", "tag 2"] | [{"image_url":"img1.jpg","severity":10}, {"image_url":"img2.jpg","severity":20}]]
(2 rows)

Note the ']]' at the end of the second row (the third would have 3 brackets, and so on).

Some info on my system (debian testing, updated a maybe 10 days ago):
$ uname -r
3.16.0-4-amd64
$ psql -V
psql (PostgreSQL) 9.4beta3

Thanks!