JSON (Java Script Object Notation) is a lightweight text-based data format that helps to interchange data easily between modern applications. At the same time, JSON is used by the NoSQL (Microsoft Azure Cosmos DB, CouchDB, etc.) databases to store the unstructured data. The small data size is the main feature of the JSON and this property enables to an interchange of the data easily between the applications. At the same time, JSON offers the following advantages:
- The JSON data structure is easily readable by humans and machines
- JSON has a compact data structure and it does not include unnecessary data notations
- JSON has extensive usage. All modern programming languages and application platforms support working with JSON
- JSON has a straightforward syntax
The JSON data structure is essentially based on a key-value pair format. The keys must be string data type and the values data types must be in JSON data type. JSON supports the following data types :
- string
- number
- boolean
- null
- object
- array
At the same time, a JSON object can contain two different data structures:
- An object type can contain multiple key-value pairs inside the JSON text
- An array can contain single or multiple values
The following JSON represents some attributes of a car brand. The color attribute represents an array and the Model attribute represents an object in this JSON object.
OPENJSON() function parses JSON in SQL Server
As we mentioned in the previous section, JSON is used widely by the applications. In this context, the conversion of the JSON data into the relational format is becoming more important.
OPENJSON is a table-valued function that helps to parse JSON in SQL Server and it returns the data values and types of the JSON text in a table format. Now, we will look at the syntax of this function.
The jsonExpression is an input parameter that specifies the JSON text that will be parsed by the OPENJSON function.
The jsonPath is an optional parameter and it is used to seek a specified JSON expression in the JSON text and the OPENJSON function parses only this part of the JSON text.
The WITH clause is an optional parameter that can be used to explicitly specifies the schema.
In the following example, we can see a very simple usage of the OPENJSON function. It will parse JSON in SQL Server and return the parsed JSON in the default schema format.
In this result set, we can observe that the OPENJSON function is executed with the default schema and it has returned three columns:
- The key column indicates the name of the key
- The value column shows the value of the key
- The type column indicates the data types of the key column through the numbers. The following table illustrates the possible values of the type column and their data type explanations
