OPENJSON with an explicit schema

If you need more control over formatting when it is offered by default, you can explicitly specify your own schema. The function will still return a table but with the columns defined by you. To specify the resultant table schema, use the WITH clause of the OPENJSON function. Here is the syntax for the OPENJSON function with an explicit schema:

OPENJSON( jsonExpression [ , path ] )   
[   
   WITH (    
       column_name data_type [ column_path ] [ AS JSON ]   
   [ , column_name data_type [ column_path ] [ AS JSON ] ]   
   [ , . . . n ]    
      )   
]   

When you use the WITH clause, you need to specify at least one column. For each column, you can specify the following attributes:

  • column_name: This is the name of the output column.
  • data_type: This ...

Get Mastering SQL Server 2017 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.