SQL Server nest JSON output-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

This comes pretty close but the output is certainly not valid JSON (and so it's going to be pretty difficult to get that output using any of the JSON functionality built-in since SQL Server 2016). Instead we can use STRING_AGG() (assuming 2017 or better - please always specify version):

DECLARE @cr nchar(2) = nchar(13)   nchar(10), @sp nchar(2) = SPACE(2);
DECLARE @csp nchar(6) = @cr   @sp   @sp;

;WITH agg1 AS
(
  SELECT continent, country, cities = 
    STRING_AGG('city: '   city, @csp) 
  FROM dbo.countries GROUP BY continent, country
),
agg2 AS
(
  SELECT cont = continent   '{'   @cr   STRING_AGG(' '   country 
      '{'   @cr   @sp   @sp   cities   @cr   ' }', @cr)   @cr   '}'
  FROM agg1 GROUP BY continent
)
SELECT STRING_AGG(cont, @cr) FROM agg2;

Output:

Asia{
 India{
    city: new dehli
    city: hyderabad
    city: mumbai
 }
 korea{
    city: seoul
    city: busan
 }
}
Europe{
 italy{
    city: rome
    city: milan
 }
}

Example db<>fiddle