CODEWITHSUNDEEP
jsonsql-serversql-server-2019json-query
Dalton
Dalton

Reputation: 447

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

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?

Upvotes: 0

Views: 902

Answers (3)

Dalton
Dalton

Reputation: 447

I've come up with combination of JSON_MODIFY and STRING_AGG

DECLARE @out NVARCHAR(MAX) = '{}';
SELECT
 @out = JSON_MODIFY(
          @out,
          '$.' + aa.[schema],
          aa.[entities]
)
FROM 
(
    SELECT
        [schema]    = referencing_schema_name,
        [entities]  = 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
) aa

select @out

I don't know what approach is better but working with JSON output is kinda pain in SQL Server.

Upvotes: 0

Charlieface
Charlieface

Reputation: 71780

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

Upvotes: 1

Sunil Nepali
Sunil Nepali

Reputation: 62

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)

Upvotes: 0

Related Questions