SQL Server 2016 "FOR JSON PATH" - Modify JSON nesting

Question

I'm trying to change the nesting of the JSON output from a query using FOR JSON PATH. The FOR JSON AUTO query is almost what I need, but not quite.

Query using 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

Query using FOR JSON AUTO Output (snippet):

.
.
.
{
    "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
                        }
                    ]
                }
            ]
        }
    ]
},
.
.
.

Each table has all of its indexes nested properly and, within each index, all of the index's columns (these are composite indexes) are nested properly. The only change I want to make is to un-nest index_columns, making column_id part of columns, e.g.:

.
.
.
"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
    }
]
.
.
.

When I try to use FOR JSON PATH, however, it ends up nesting improperly.

Query using 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

Query using FOR JSON PATH Output (snippet):

.
.
.
{
    "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
            }
        }
    }
},
.
.
.

While column_id is now where I would like it to be, columns is now the only thing properly nested. Each table is now repeated for each of its indexes, and each index is repeated for each of its columns.

How do I get column_id where I want it (like the output from the FOR JSON PATH query), while maintaining the proper nesting (like the output from the FOR JSON AUTO query)?

UPDATE -- WORKING

Based on DimaSUN's response and Ben's comment, I came up with this query that is now working:

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')

New Query Output (snippet):

.
.
.
{
    "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
                }
            ]
        }
    ]
},
.
.
.

The AS 'columns' and AS 'indexes' at the ends of the nested selects was a critical piece because otherwise I was getting the following error:

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.

You'd typically want to do a correlated subquery for this type of thing. That is, your driving query will be select * from sys.tables as twhere name like 'tbl_%' instead of selecting "*", you'd issue a select from, for instance, sys.indexes looking something like select i.name from sys.indexes as i where i.object_id = t.object_id. The where clause says "give me indexes that are for the current table under consideration". — Ben Thul
– Ben Thul, Commented Dec 28, 2015 at 3:03
Thanks for the response, Ben! Helped a lot. See my update above. — eggroll
– eggroll, Commented Dec 28, 2015 at 7:01
@BenThul So that's mean for the above scenario we have no option to use inner join other than using sub quires? — Osama Aftab
– Osama Aftab, Commented Oct 1, 2017 at 13:30
The problem with an inner join is that you're going to produce one row per match. Say, for instance, you have a relationship where each row in tableA has 10 rows in tableB. If tableA has 5 rows and you do select * from tableA join tableB, you're going to get back 5x10=50 rows. You can turn that into JSON, but most often, you're going to want JSON with (from my example) 5 elements each of which has 10 properties. — Ben Thul
– Ben Thul, Commented Oct 1, 2017 at 23:45

DimaSUN · Accepted Answer · 2015-12-28 01:36:48Z

1

for XML it looks like

  ( 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')

replace XML with JSON.

answered Dec 28, 2015 at 1:36

DimaSUN

9218 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eggroll Over a year ago

Thanks for the detailed answer, DimaSUN! It got me 99% there. I just needed to play with it a bit due to apparently slight differences in syntax between FOR JSON PATH and FOR XML PATH. See my update above. Cheers!

Collectives™ on Stack Overflow

SQL Server 2016 "FOR JSON PATH" - Modify JSON nesting

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related