Converting JSON data in a tabular format

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 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...