Re: JSON_AGG produces extra square brakets

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

==================== DB SETUP ====================

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!

On Sun, Nov 30, 2014 at 11:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Davide S <swept(dot)along(dot)by(dot)events(at)gmail(dot)com> writes:
> > Note that the tags are just fine, but the arrays with the states have an
> > increasing number of square brackets at the end: the first has 1
> (correct),
> > the second has 2, the third has 3, etc., which is invalid json.
>
> Could you provide a self-contained test case for that?
>
> regards, tom lane
>