PostgreSQL: json_agg in join results in nested array

Question

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 "}]   |

You don't clearly say what your query should return. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. Please in code questions give a minimal reproducible example--cut & paste & runnable code; example input (as tabular initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy
– philipxy, Commented Sep 7, 2019 at 21:18
The JSON_AGG function you're using will always return values as an array. Can you update your question with some sample rows of how you want to see your data returned? Also, if you can post a SQL Fiddle with the DDL and sample data to play with that would make it easier for us to help you out. Here's one I started that you can modify - sqlfiddle.com/#!17/22202 — ravioli
– ravioli, Commented Sep 8, 2019 at 5:59
Hi @ravioli, thanks for your inputs. I have updated your sqlfiddle.com/#!17/22202 url and added the desired output required. — Akshay komarla
– Akshay komarla, Commented Sep 8, 2019 at 8:26
since i'm using JSON_AGG twice it results in [[ .... ]]. I want the output as [...] — Akshay komarla
– Akshay komarla, Commented Sep 8, 2019 at 8:27

ravioli · Accepted Answer · 2019-09-08 09:27:21Z

5

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

answered Sep 8, 2019 at 9:27

ravioli

3,8213 gold badges17 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ravioli Over a year ago

No prob bob. :)

Collectives™ on Stack Overflow

PostgreSQL: json_agg in join results in nested array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related