Search Postgresql Archives

JSON_AGG produces extra square brakets

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux