SQL to generate JSON in specific format-CodePudding

I am using MS SQL. I have this Table:

SELECT id, description, contacts, type FROM Table1 WHERE id = 'AA'

Output:

id  description        contacts  type
AA  Albany Associates  12        static
AA  Albany Associates  23        static
AA  Albany Associates  13        static
AA  Albany Associates  45        static
AA  Albany Associates  23        static
AA  Albany Associates  43        static

Anyone please help me how should I write SQL to get an JSON output like this:

{"id":"AA",
"description":"Albany Associates",
"members":
    {
        "contacts":
                    ["12","23","13","45","43","23","43"]},
        "type":     "Static"
    }

I tried this:

    SELECT id, description, contacts, type FROM Table1 WHERE id = 'AA' FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER

and output came like this:

 {"id":"AA",
"description":"Albany Associates",
"contact_1004_contactID":"12",
"type":"static"}
,{"id":"AA",
"description":"Albany Associates",
"contact_1004_contactID":"23",
"type":"static"}

CodePudding user response：

Setup:

SELECT *
INTO Table1
FROM (VALUES
('AA','Albany Associates','12','static'),
('AA','Albany Associates','23','static'),
('AA','Albany Associates','13','static'),
('AA','Albany Associates','45','static'),
('AA','Albany Associates','23','static'),
('AA','Albany Associates','43','static')) 
T(id, description, contacts, [type])

Query:

SELECT 
    id,
    description, 
    JSON_QUERY(
        (SELECT '['  
            STRING_AGG('"'   STRING_ESCAPE(contacts, 'json')   '"', ',')  
         ']')) AS "members.contacts",
    [type] AS "type"
FROM Table1
WHERE id = 'AA'
GROUP BY id, description, [type]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Output (pretty-printed, which SQL Server doesn't natively do):

{
    "id": "AA",
    "description": "Albany Associates",
    "members": {
        "contacts": ["12", "23", "13", "45", "23", "43"]
    },
    "type": "static"
}

Note the clunky way in which we have to build the string array. Unfortunately T-SQL has no native function for building a JSON scalar array. Given the input we don't strictly need to include the STRING_ESCAPE call (since there are no special characters) but it's safer to include it anyway, in case there might be.

SQL Server 2016 doesn't have STRING_AGG, so this gets even uglier:

SELECT 
    id,
    description, 
    JSON_QUERY('['   STUFF(
        (
            SELECT ',"'   STRING_ESCAPE(contacts, 'json')   '"'
            FROM Table1 T2 
            WHERE T2.id = T1.id
            FOR XML PATH(''), TYPE
        ).value('text()[1]', 'NVARCHAR(MAX)'), 1, 1, '') 
      ']') AS "members.contacts",
    [type] AS "type"
FROM Table1 T1
WHERE id = 'AA'
GROUP BY id, description, [type]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Now we need to take both XML and JSON escaping into consideration, fun. This query assumes id is sufficiently unique to join on, which happens to be the case for this particular table.