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.
