title: SQL Server 2016 "FOR JSON PATH" - Modify JSON nesting
body: <p>I'm trying to change the nesting of the JSON output from a query using <code>FOR JSON PATH</code>.  The <code>FOR JSON AUTO</code> query is almost what I need, but not quite.</p>
  
  <p>Query using <code>FOR JSON AUTO</code>:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Query using <code>FOR JSON AUTO</code> Output (snippet):</p>
  
  <pre><code>.
  .
  .
  {
      "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
                          }
                      ]
                  }
              ]
          }
      ]
  },
  .
  .
  .
  </code></pre>
  
  <p>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 <code>index_columns</code>, making <code>column_id</code> part of <code>columns</code>, e.g.:</p>
  
  <pre><code>.
  .
  .
  "columns": [
      {
          "column_name": "PartitionId",
          "max_length": 4,
          "precision": 10,
          "scale": 0,
    ---&gt;  "column_id": 1
      },
      {
          "column_name": "PoolId",
          "max_length": 4,
          "precision": 10,
          "scale": 0,
    ---&gt;  "column_id": 2
      },
      {
          "column_name": "AgentName",
          "max_length": 128,
          "precision": 0,
          "scale": 0,
    ---&gt;  "column_id": 3
      }
  ]
  .
  .
  .
  </code></pre>
  
  <p>When I try to use <code>FOR JSON PATH</code>, however, it ends up nesting improperly.</p>
  
  <p>Query using <code>FOR JSON PATH</code>:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Query using <code>FOR JSON PATH</code> Output (snippet):</p>
  
  <pre><code>.
  .
  .
  {
      "tables": {
  ===&gt;    "table_name": "tbl_Agent",
          "table_object_id": 176055713,
          "indexes": {
    ---&gt;      "index_name": "PK_Task_tbl_Agent",
              "index_id": 1,
              "columns": {
                  "column_name": "PartitionId",
                  "column_id": 1,
                  "max_length": 4,
                  "precision": 10,
                  "scale": 0
              }
          }
      }
  },
  {
      "tables": {
  ===&gt;    "table_name": "tbl_Agent",
          "table_object_id": 176055713,
          "indexes": {
    ---&gt;      "index_name": "PK_Task_tbl_Agent",
              "index_id": 1,
              "columns": {
                  "column_name": "AgentId",
                  "column_id": 2,
                  "max_length": 4,
                  "precision": 10,
                  "scale": 0
              }
          }
      }
  },
  {
      "tables": {
  ===&gt;    "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
              }
          }
      }
  },
  .
  .
  .
  </code></pre>
  
  <p>While <code>column_id</code> is now where I would like it to be, <code>columns</code> is now the only thing properly nested.  Each <code>table</code> is now repeated for each of its <code>indexes</code>, and each <code>index</code> is repeated for each of its <code>columns</code>.</p>
  
  <p><strong>How do I get <code>column_id</code> where I want it (like the output from the <code>FOR JSON PATH</code> query), while maintaining the proper nesting (like the output from the <code>FOR JSON AUTO</code> query)?</strong></p>
  
  <p><strong>UPDATE -- WORKING</strong></p>
  
  <p>Based on DimaSUN's response and Ben's comment, I came up with this query that is now working: </p>
  
  <pre><code>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')
  </code></pre>
  
  <p>New Query Output (snippet):</p>
  
  <pre><code>.
  .
  .
  {
      "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
                  }
              ]
          }
      ]
  },
  .
  .
  .
  </code></pre>
  
  <p>The <code>AS 'columns'</code> and <code>AS 'indexes'</code> at the ends of the nested selects was a critical piece because otherwise I was getting the following error:</p>
  
  <pre><code>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.
  </code></pre>