I'm using JSON_AGG to create some arrays, but I get an invalid json (I'm
using the latest postgres 9.4 in debian testing).
Quick explanation: I've got some objects called Things that have Tags
(many-to-many through the table ThingTag); Things also have a single
ThingTemplate, which has ThingStates (many-to-many), and each ThingState
has a single SummaryStatus.
For each Thing, I want to get a json array with all the tags, as well as a
json array with all the states.
This is the query (forgive the CamelCase):
SELECT
th.id, tags, xtst.states
FROM
"Thing" th,
(SELECT tt."thingId" AS thid, JSON_AGG( tg.name ) AS "tags" FROM
"ThingTag" tt, "Tag" tg WHERE (tt."tagId" = tg.id) GROUP BY tt."thingId")
xtg,
(SELECT tst."thingTemplateId", JSON_AGG( ROW_TO_JSON( (SELECT q FROM
(SELECT tst."imageUrl") q) ) ) AS "states" FROM "ThingState" tst,
"SummaryStatus" sst WHERE (tst."summaryStatusId" = sst.id) GROUP BY
tst."thingTemplateId") xtst
WHERE (xtg.thid = th.id) AND (xtst."thingTemplateId" = th."templateId") AND
(th.id IN (1, 12, 23));
This is the output:
id | tags | states
23 | ["Public tag 1", "Site C tag 1"] | [{"imageUrl":"img.png"},
{"imageUrl":"img.png"}, {"imageUrl":"img.png"}, {"imageUrl":"img.png"},
{"imageUrl":"img.png"}, {"imageUrl":"img.png"}]
1 | ["Public tag 1", "Site A tag 2"] |
[{"imageUrl":"thingLoad_Normal.png"}, {"imageUrl":"thingLoad_Normal.png"},
{"imageUrl":"thingLoad_Fault.png"}, {"imageUrl":"thingLoad_Fault.png"},
{"imageUrl":"thingLoad_Alarm.png"}, {"imageUrl":"thingLoad_Alarm.png"}]]
12 | ["Public tag 1", "Site B tag 1"] | [{"imageUrl":"img.png"},
{"imageUrl":"img.png"}, {"imageUrl":"img.png"}, {"imageUrl":"img.png"},
{"imageUrl":"img.png"}, {"imageUrl":"img.png"}]]]
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.
The extra square brackets go away if I:
* select just one (any) thingId (instead of (1,12,23))
* remove the subquery for the tags
* remove the (FROM "SummaryStatus" sst) from the states subquery
* change the second JSON_AGG() to ARRAY_TO_JSON(ARRAY_AGG())
It seems really weird to me, am I doing something wrong?
Thanks.
David
