SQL Server 2016 "FOR JSON PATH" – Modification de l'imbrication JSON

J'essaie de modifier l'imbrication de la sortie JSON d'une requête à l'aide de FOR JSON PATH . La requête FOR JSON AUTO est presque ce dont j'ai besoin, mais pas tout à fait.

Requête utilisant FOR JSON AUTO :

 SELECT table_name = tables.name, table_object_id = tables.object_id, index_name = indexes.name, index_id = indexes.index_id, column_name = columns.name, column_id = index_columns.index_column_id, max_length = columns.max_length, precision = columns.precision, scale = columns.scale FROM sys.indexes indexes INNER JOIN sys.index_columns index_columns ON indexes.object_id = index_columns.object_id and indexes.index_id = index_columns.index_id INNER JOIN sys.columns columns ON index_columns.object_id = columns.object_id and index_columns.column_id = columns.column_id INNER JOIN sys.tables tables ON indexes.object_id = tables.object_id WHERE tables.name LIKE 'tbl_%' ORDER BY tables.name, indexes.index_id, index_columns.index_column_id FOR JSON AUTO 

Requête utilisant FOR JSON AUTO Output (extrait):

 . . . { "table_name": "tbl_Agent", "table_object_id": 176055713, "indexes": [ { "index_name": "PK_Task_tbl_Agent", "index_id": 1, "columns": [ { "column_name": "PartitionId", "max_length": 4, "precision": 10, "scale": 0, "index_columns": [ { "column_id": 1 } ] }, { "column_name": "AgentId", "max_length": 4, "precision": 10, "scale": 0, "index_columns": [ { "column_id": 2 } ] } ] }, { "index_name": "IX_Task_tbl_Agent_PoolId_AgentName", "index_id": 2, "columns": [ { "column_name": "PartitionId", "max_length": 4, "precision": 10, "scale": 0, "index_columns": [ { "column_id": 1 } ] }, { "column_name": "PoolId", "max_length": 4, "precision": 10, "scale": 0, "index_columns": [ { "column_id": 2 } ] }, { "column_name": "AgentName", "max_length": 128, "precision": 0, "scale": 0, "index_columns": [ { "column_id": 3 } ] } ] }, { "index_name": "IX_Task_tbl_Agent_PoolId_SessionId", "index_id": 3, "columns": [ { "column_name": "PartitionId", "max_length": 4, "precision": 10, "scale": 0, "index_columns": [ { "column_id": 1 } ] }, { "column_name": "PoolId", "max_length": 4, "precision": 10, "scale": 0, "index_columns": [ { "column_id": 2 } ] }, { "column_name": "SessionId", "max_length": 16, "precision": 0, "scale": 0, "index_columns": [ { "column_id": 3 } ] } ] } ] }, . . . 

Chaque table a tous ses index nesteds correctement et, dans chaque index, toutes les colonnes de l'index (ce sont des index composites) sont nestedes correctement. Le seul changement que je veux faire est de index_columns , faisant de column_id partie des columns , par exemple:

• Comment supprimer le personnage principal avant un personnage spécifique?
• Requête SQL Server Output vers UTF8 au lieu de UTF16
• Synchronisation de la database Android avec MSSQL
• Obtention de données à partir de plusieurs tables (Entité C #), liées par des foreign keys, converties en object anonyme
• Comment forcer SQL Server à returnner un tableau JSON vide

 . . . "columns": [ { "column_name": "PartitionId", "max_length": 4, "precision": 10, "scale": 0, ---> "column_id": 1 }, { "column_name": "PoolId", "max_length": 4, "precision": 10, "scale": 0, ---> "column_id": 2 }, { "column_name": "AgentName", "max_length": 128, "precision": 0, "scale": 0, ---> "column_id": 3 } ] . . . 

Lorsque j'essaie d'utiliser FOR JSON PATH , cependant, il finit par imbriquer incorrectement.

Requête utilisant FOR JSON PATH :

 SELECT tables.name AS [tables.table_name], tables.object_id AS [tables.table_object_id], indexes.name AS [tables.indexes.index_name], indexes.index_id AS [tables.indexes.index_id], columns.name AS [tables.indexes.columns.column_name], index_columns.index_column_id AS [tables.indexes.columns.column_id], columns.max_length AS [tables.indexes.columns.max_length], columns.precision AS [tables.indexes.columns.precision], columns.scale AS [tables.indexes.columns.scale] FROM sys.indexes indexes INNER JOIN sys.index_columns index_columns ON indexes.object_id = index_columns.object_id and indexes.index_id = index_columns.index_id INNER JOIN sys.columns columns ON index_columns.object_id = columns.object_id and index_columns.column_id = columns.column_id INNER JOIN sys.tables tables ON indexes.object_id = tables.object_id WHERE tables.name LIKE 'tbl_%' ORDER BY tables.name, indexes.index_id, index_columns.index_column_id FOR JSON PATH 

Requête utilisant FOR JSON PATH Output (extrait):

 . . . { "tables": { ===> "table_name": "tbl_Agent", "table_object_id": 176055713, "indexes": { ---> "index_name": "PK_Task_tbl_Agent", "index_id": 1, "columns": { "column_name": "PartitionId", "column_id": 1, "max_length": 4, "precision": 10, "scale": 0 } } } }, { "tables": { ===> "table_name": "tbl_Agent", "table_object_id": 176055713, "indexes": { ---> "index_name": "PK_Task_tbl_Agent", "index_id": 1, "columns": { "column_name": "AgentId", "column_id": 2, "max_length": 4, "precision": 10, "scale": 0 } } } }, { "tables": { ===> "table_name": "tbl_Agent", "table_object_id": 176055713, "indexes": { "index_name": "IX_Task_tbl_Agent_PoolId_AgentName", "index_id": 2, "columns": { "column_name": "PartitionId", "column_id": 1, "max_length": 4, "precision": 10, "scale": 0 } } } }, . . . 

Alors que column_id est maintenant là où je voudrais que ce soit, les columns sont maintenant la seule chose correctement nestede. Chaque table est maintenant répétée pour chacun de ses indexes , et chaque index est répété pour chacune de ses columns .

Comment puis-je get column_id où je le veux (comme la sortie de la requête FOR JSON PATH ), tout en maintenant l'imbrication correcte (comme la sortie de la requête FOR JSON AUTO )?

MISE À JOUR – TRAVAIL

Basé sur la réponse de DimaSUN et le commentaire de Ben, j'ai trouvé cette requête qui fonctionne maintenant:

 SELECT tables.name AS [table_name], tables.object_id AS [table_object_id], (SELECT indexes.name AS [index_name], indexes.index_id AS [index_id], (SELECT columns.name AS [column_name], index_columns.index_column_id AS [column_id], columns.max_length AS [max_length], columns.precision AS [precision], columns.scale AS [scale] FROM sys.index_columns index_columns JOIN sys.columns columns ON index_columns.object_id = columns.object_id and index_columns.column_id = columns.column_id WHERE indexes.object_id = index_columns.object_id and indexes.index_id = index_columns.index_id ORDER BY index_columns.index_column_id FOR JSON PATH ) AS 'columns' FROM sys.indexes indexes WHERE indexes.object_id = tables.object_id ORDER BY indexes.index_id FOR JSON PATH ) AS 'indexes' FROM sys.tables tables WHERE tables.name LIKE 'tbl_%' ORDER BY tables.name FOR JSON PATH, ROOT('tables') 

Nouvelle sortie de requête (extrait):

 . . . { "table_name": "tbl_Agent", "table_object_id": 176055713, "indexes": [ { "index_name": "PK_Task_tbl_Agent", "index_id": 1, "columns": [ { "column_name": "PartitionId", "column_id": 1, "max_length": 4, "precision": 10, "scale": 0 }, { "column_name": "AgentId", "column_id": 2, "max_length": 4, "precision": 10, "scale": 0 } ] }, { "index_name": "IX_Task_tbl_Agent_PoolId_AgentName", "index_id": 2, "columns": [ { "column_name": "PartitionId", "column_id": 1, "max_length": 4, "precision": 10, "scale": 0 }, { "column_name": "PoolId", "column_id": 2, "max_length": 4, "precision": 10, "scale": 0 }, { "column_name": "AgentName", "column_id": 3, "max_length": 128, "precision": 0, "scale": 0 } ] }, { "index_name": "IX_Task_tbl_Agent_PoolId_SessionId", "index_id": 3, "columns": [ { "column_name": "PartitionId", "column_id": 1, "max_length": 4, "precision": 10, "scale": 0 }, { "column_name": "PoolId", "column_id": 2, "max_length": 4, "precision": 10, "scale": 0 }, { "column_name": "SessionId", "column_id": 3, "max_length": 16, "precision": 0, "scale": 0 } ] } ] }, . . . 

Les AS 'columns' AS 'indexes' et les AS 'indexes' aux extrémités des sélections nestedes étaient une pièce critique car sinon j'obtenais l'erreur suivante:

 Msg 13605, Level 16, State 1, Line 1 Unnamed tables cannot be used as JSON identifiers as well as unnamed columns cannot be used as key names. Add alias to the unnamed column/table. 

• Récupération et utilisation de la sortie d'une requête "FOR JSON" dans une variable
• Rendre json à partir de la requête sql
• Pourquoi JSON n'accepte-t-il pas une procédure stockée qui transmet une variable en tant qu'ID?
• Impossible d'interroger correctement (MSSQL – PHP – JSON)
• SQL Server Convertir XML en JSON

  ( select indexes.name AS [index_name], indexes.index_id AS [index_id], ( select columns.name AS [column_name], index_columns.index_column_id AS [column_id], columns.max_length AS [max_length], columns.precision AS [precision], columns.scale AS [scale] from sys.index_columns index_columns JOIN sys.columns columns ON index_columns.object_id = columns.object_id and index_columns.column_id = columns.column_id where indexes.object_id = index_columns.object_id and indexes.index_id = index_columns.index_id FOR xml PATH(''), root('columns'), type ) from sys.indexes indexes where indexes.object_id = tables.object_id FOR xml PATH(''), root('indices'), type ) FROM sys.tables tables WHERE tables.name LIKE 'tbl_%' --ORDER BY tables.name, indexes.index_id, index_columns.index_column_id FOR XML PATH('tables')