SQL Server - How to create custom JSON from SELECT with "key": derived from GROUP BY claus-CodePudding

I am trying to get nice overview of dependencies of tables.

My query looks like this: (if there is something better than STRING_AGG, please let me know)

SELECT 
    [schema] = referencing_schema_name,
    [objects] = JSON_QUERY('["' 
          STRING_AGG(STRING_ESCAPE(referencing_entity_name, 'json'), '","') 
          '"]')
FROM sys.dm_sql_referencing_entities (
    'dbo.C_Table',
    'OBJECT' ) re
GROUP BY referencing_schema_name
FOR JSON PATH;

And it produces output like:

[
    {
        "schema": "dbo",
        "objects": [
            "C_Table"
        ]
    },
    {
        "schema": "bcd",
        "objects": [
            "get_AmazingDataById",
            "get_EvenMoreAmazingDataById"
        ]
    }
]

So grouping works, but I'd like to have it "nicer" :D and more readable like this:

[
   "dbo": [
       "C_Table"
   ],
   "bcd": [
       "get_AmazingDataById",
       "get_EvenMoreAmazingDataById"
   ]
]

Do you have some suggestions for creating an array and using the value of group by as a key for that array?

CodePudding user response：

You can try using json_Query and JSON_Modify combination as given example.

DECLARE @jsonInfo NVARCHAR(MAX)



SET @jsonInfo=N'
{"68c4":["yes"], "c8ew":["0","1"], "p6i4":["London","Frankfurt","Tokyo"]}'
SELECT JSON_MODIFY(
JSON_MODIFY(@jsonInfo, '$.City', JSON_QUERY(@jsonInfo, '$.p6i4')),
'$.p6i4', NULL)

CodePudding user response：

Unfortunately, SQL Server doesn't support either JSON_AGG or JSON_OBJ_AGG, both of which would have made this query significantly simpler.

You can just hack the whole thing with STRING_AGG a second time.

SELECT
    '['  
    STRING_AGG(
      '{"'  
      STRING_ESCAPE(referencing_schema_name, 'json')  
      '":'  
      re.jsonArray
    , ',')  
    ']'
FROM (
    SELECT
      re.referencing_schema_name,
      jsonArray = '["' 
          STRING_AGG(STRING_ESCAPE(re.referencing_entity_name, 'json'), '","') 
          '"]'
    FROM sys.dm_sql_referencing_entities ('dbo.C_Table', 'OBJECT' ) re
    GROUP BY
      re.referencing_schema_name
) re;

db<>fiddle