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: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));
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"}]]]
* select just one (any) thingId (instead of (1,12,23))
* remove the subquery for the tagsDavid