Nowadays, JSON is a recognized format for data representation and exchange. However, most of the existing data still resides in relational databases and you need to combine them to process and manipulate them together. In order to combine JSON with relational data or to import it in relational tables, you need to map JSON data to tabular data, that is, convert it into a tabular format. In SQL Server 2016, you can use the OPENJSON function to accomplish this:

• OPENJSON is a newly added rowset function. A rowset function is a table-valued function and returns an object that can be used as if it were a table or a view. Just as OPENXML provides a rowset view over an XML document, OPENJSON gives a rowset view over JSON data. The OPENJSON function converts JSON objects and properties to table rows and columns respectively.
• It accepts two input arguments:
  • Expression: JSON text in the Unicode format.
  • Path: This is an optional argument. It is a JSON path expression and you can use it to specify a fragment of the input expression.

The function returns a table with a default or user-defined schema.

To use the OPENJSON function, the database must be in compatibility level 130. If it is not, you will get the following error:

Msg 208, Level 16, State 1, Line 78
Invalid object name 'OPENJSON'.

As mentioned, the returned table can have an implicit (default) schema or an explicit one, defined by the user. In the next two sections, both schemas will be explored in more detail.