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:
OPENJSONis a newly addedrowsetfunction. Arowsetfunction is a table-valued function and returns an object that can be used as if it were a table or a view. Just asOPENXMLprovides a rowset view over an XML document,OPENJSONgives a rowset view over JSON data. TheOPENJSONfunction 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...