How to query all system under SystemTypes in SQL database in a json format

Question

I have a SQL table like below

GroupID	CountryName	CityName
1	India	Nellore
1	India	Chittoor
1	SriLanka	Kadapa
2	China	Beijing
2	China	Vwk

i want to output as json format as below

[
  {
    "GroupId": 1,
    "Data": [
      {
        "Country" : "India",
        "City" : ["India" || "Nellore"] 
      },
      {
        "Country" : "SriLanka",
        "City" : ["Kadapa"] 
      }
    ]
  },
  {
    "GroupId": 2,
    "Data": [
      {
        "Country" : "China",
        "City" : ["Beijing" || "Vwk"] 
      }
    ]
  },
]

I tried to achieve this by different queries but didn't get it

Could some one help me with this query

Answer 1

You need two levels of aggregation here, plus a final FOR JSON.

Unfortunately, SQL Server does not support JSON_AGG or JSON_OBJECT_AGG which would have made this easier. You need to hack it with a combination of STRING_ESCAPE and STRING_AGG, as well as JSON_QUERY to prevent double-escaping.

WITH ByCountry AS (
    SELECT
      t.GroupId,
      t.CountryName,
      City = '[' + STRING_AGG('"' + STRING_ESCAPE(t.CityName, 'json') + '"', ',') + ']'
    FROM YourTable t
    GROUP BY
      t.GroupId,
      t.CountryName
)
SELECT
  c.GroupId,
  Data = JSON_QUERY('[' + STRING_AGG(j.json, ',') + ']')
FROM ByCountry c
CROSS APPLY (
  SELECT
    c.GroupId,
    c.CountryName,
    City = JSON_QUERY(c.City)
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j(json)
GROUP BY
  c.GroupId
FOR JSON PATH;

db<>fiddle