SQL Server nest JSON output

Question

This is my SQL Server table:

sql table

I have a table like this:

CREATE TABLE countries
(
    continent nvarchar(10),
    country nvarchar(10),
    city nvarchar(10),
);

And I have this data like this:

INSERT INTO countries
VALUES ('asia', 'inda', 'new delhi'),
       ('asia', 'inda', 'hyderabad'),
       ('asia', 'inda', 'mumbai'),
       ('asia', 'korea', 'seoul'),
       ('asia', 'inda', 'milan'),
       ('europe', 'italy', 'rome'),
       ('europe', 'italy', 'milan');

I need JSON output in this format:

Asia 
{
  india
  {
      city: new delhi
      city: Hyderabad
      city: Mumbai
  }
  Korea 
  {
      city: seoul
      city: busan
  }
}
Europe
{
  Italy
  {
      city: rome
      city:milan
      city:naples
  } 
}

I tried so many queries, but I am not getting it:

select continent, country, city 
from countries 
group by continent, country 
for json auto

which doesn't return the desired output.

Not without Dynamic SQL or at least some string manipulation. Posting images of sample data is not very useful. Please post sample data as formatted text — John Cappelletti, Aug 13 '22 at 18:35
You also may want to post some valid JSON as the desired results — John Cappelletti, Aug 13 '22 at 18:47

score 1 · Answer 1 · answered Aug 14 '22 at 00:14

Assuming the output you wanted was actually real JSON (and not the pseudo-JSON you posted), it is possible if difficult to get dynamic keys.

Unfortunately, SQL Server does not have JSON_AGG nor JSON_OBJ_AGG which would have made this much easier. Instead we need to hack STRING_AGG and STRING_ESCAPE

WITH cities AS (
    SELECT
      c.continent,
      c.country,
      json = '[' + STRING_AGG(j.json, ',') + ']'
    FROM countries c
    CROSS APPLY (
        SELECT
          c.city
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) j(json)
    GROUP BY
      c.continent,
      c.country
),
countries AS (
    SELECT
      c.continent,
      json = '{' + STRING_AGG('"' + STRING_ESCAPE(c.country, 'json') + '":' + c.json, ',') + '}'
    FROM cities c
    GROUP BY
      c.continent
)
SELECT
  '{' + STRING_AGG('"' + STRING_ESCAPE(c.continent, 'json') + '":' + c.json, ',') + '}'
FROM countries c;

db<>fiddle

Result:

{
  "asia": {
    "inda": [
      {
        "city": "new delhi"
      },
      {
        "city": "hyderabad"
      },
      {
        "city": "mumbai"
      },
      {
        "city": "milan"
      }
    ],
    "korea": [
      {
        "city": "seoul"
      }
    ]
  },
  "europe": {
    "italy": [
      {
        "city": "rome"
      },
      {
        "city": "milan"
      }
    ]
  }
}

SQL Server nest JSON output

1 Answers1