I have a SQL table like below

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

CodePudding user response：

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