CODEWITHSUNDEEP
sqlpostgresqljoinaggregate-functions
Akshay komarla
Akshay komarla

Reputation: 744

PostgreSQL: json_agg in join results in nested array

For my problem, I'm using json_agg in join to aggregate my result. But this results in a nested array sequence.

Query: 

 SELECT c.*,
       json_agg(ci.national_id) AS national_id,
       json_agg(a.address) AS address
FROM company AS c
LEFT JOIN
  (SELECT company_id,
          json_agg(json_build_object('value', national_id, 'country', country_code)) AS national_id
   FROM company_identification
   GROUP BY company_id) AS ci ON ci.company_id = c.id
LEFT JOIN
  (SELECT company_id,
          json_agg(address.*) AS address
   FROM address
   GROUP BY company_id) AS a ON a.company_id = c.id
GROUP BY c.id

Result: 

 [
  {
    "id": "c876967d-dd8b-4068-88f4-57a438a2015c",
    "name": "S.A.1",
    "nationalId": [
      [
        {
          "value": "string",
          "country": "CL"
        }
      ]
    ],
    "address": [
      [
        {
          "id": "d1362084-e652-4900-ba51-86352b7a8ce5",
          "streetName": "First Avenue"
        },
        {
          "id": "0f785a23-6eb3-44ea-9254-34a6f47ff638",
          "streetName": "Second Avenue"
        }
      ]
    ]
  },
  {
    "id": "38557302-a6a3-4484-ae1b-27edc8c4e906",
    "name": "S.A.",
    "nationalId": [
      [
        {
          "value": "Chile",
          "country": "CL"
        },
        {
          "value": "Colombia",
          "country": "CO"
        },
        {
          "value": "Mexico",
          "country": "MX"
        }
      ]
    ],
    "address": [
      [
        {
          "id": "d1362084-e652-4900-ba51-86352b7a8ce5",
          "streetName": "First Avenue"
        },
        {
          "id": "0f785a23-6eb3-44ea-9254-34a6f47ff638",
          "streetName": "Second Avenue"
        }
      ]
    ]
  }
]

As you can see the nationalId and address fileds contains nested arrays.

sqlfiddle: http://www.sqlfiddle.com/#!17/9fde6/2

Output from sqlfiddle:

| id |  name |                                   national_id |
|----|-------|-----------------------------------------------|
|  1 | S.A.1 | [[{"value" : "Chile", "country" : "CL "}]]    |
|  2 |  S.A. | [[{"value" : "Colombia", "country" : "CO "}]] |

The national_id field should not contain array within array

[[ ... ]]

Desired Output:

| id |  name |                                   national_id |
|----|-------|-----------------------------------------------|
|  1 | S.A.1 | [{"value" : "Chile", "country" : "CL "}]      |
|  2 |  S.A. | [{"value" : "Colombia", "country" : "CO "}]   |

Upvotes: 3

Views: 4500

Answers (1)

ravioli
ravioli

Reputation: 3833

Thanks for posting the example to SQL Fiddle. I think the reason for your double array is that you're using JSON_AGG around national_id in the outer SELECT. Try removing it and the GROUP BY:

SELECT 
  "c".*,
--  JSON_AGG(ci.national_id) AS national_id
  ci.national_id
FROM "company" AS "c"
LEFT JOIN(
  SELECT "company_id",
    JSON_AGG(
      JSON_BUILD_OBJECT(
        'value', national_id, 
        'country', country_code
      )
    ) AS national_id
  FROM "company_identification"
  GROUP BY "company_id"
) AS "ci" ON "ci"."company_id" = "c"."id"
LEFT JOIN(
  SELECT "company_id",
    JSON_AGG(address.*) AS address
  FROM "address"
  GROUP BY "company_id"
) AS "a" ON "a"."company_id" = "c"."id"
-- GROUP BY "c"."id", "c"."name"
;

You already did the JSON_AGG in the two LEFT JOINs for national_id and address and they are both grouped by company_id, so there should be no need for an additional JSON_AGG in your outer SELECT.

Let me know if that works.

http://www.sqlfiddle.com/#!17/9fde6/8

Upvotes: 5

Related Questions