What is JSON?
Data transmission should be done quickly and asynchronously without affecting the page rendering speed. The JSON structure transfers data without impacting a user’s interaction and awareness.
Here is an example of JSON data that stores personal information.
Common Uses of JSON:
- Store unstructured data in log files
- Store data in NoSQL databases
- Send and receive data in REST Web services
- REST endpoints that accept/return JSON are built into Azure Web services like Azure Storage and Azure Search
- Asynchronous calls for data transfer between web pages and servers
Microsoft introduced built-in JSON functions in SQL Server to align with the web services that use JSON for their data transmission.
- Parse JSON format data
- Read or modify values
- Transform arrays of objects in JSON format to a table
- Transform results from a table into JSON Format
JSON Path Expressions:
JSON paths identify the properties of JSON objects. The syntax for JSON paths are similar to Jscript:
Defining a path consists of two components:
- Path mode (optional) – should contain one of these mode values:
- Lax (default mode): If the path expression has an error, then the function will return an empty value. This is the default mode.
- Strict: If the path expression has an error then the function will return an error.
- Path – a set of path steps specified using the following operators and elements:
- Key names: If the key names contain any special character, it should be surrounded by quotation marks. For example, $.”user name”.
- Context item: Represented by a dollar sign ($).
- Array elements: Arrays are zero-based. For example, $.name.
- Member of an object: Represented by the dot operator.
Available Built-in Functions:
|Checks whether a valid JSON available in the given string||ISJSON (<string for test JSON>) |
Returns 1 if it is JSON, 0 otherwise
ISJSON() can also be used in SELECT queries to select a result set based on the value return from ISJSON() function.
|Extracts a scalar (single) value from a JSON String||JSON_VALUE (expression, path) Expression: a variable or a table column containing JSON text.· Prompts an error if the given expression is invalid Path: specifies the property to be extracted from the JSON expression· Prompts an error if the given path is in an invalid format Returns the outputasnvarchar (4000)|
If the value of the output is greater than 4000 characters, JSON_VALUE:
|Extracts an array or object from the given JSON string||JSON_QUERY (expression, path) Returns the outputasnvarchar(max)||If the value is not an array or object, JSON_QUERY:· returns null in lax mode· returns an error in strict mode|
|Updates a property value in the specified JSON string and returns the updated string||JSON_MODIFY (expression, path, value) |
Path: specifies the property to be updated
[append] [lax | strict] $.<json.path>
New value: the new value for the property in the specified JSON path.
- Lax mode: JSON_MODIFY deletes the specified key if the new value is NULL.
Returns the updated and formatted JSON text
|Returns the given JSON text in a collection of rows and columns.||OPENJSON(@json) Runs SQL queries on a JSON collection |
Imports JSON text to SQL tables
In this mode, the function parses the details and returns the results as
With Schema: the function returns a table with the specified columns as per the specified schema for the output.
|Accepts single or collection of JSON objects |
Converts JSON array’s each element into a new row in the output table.
About FOR JSON:
FOR JSON is a feature used to format query results as JSON.
Add this clause to a SELECT Statement to export the data from SQL Server in JSON.
You can explicitly specify the JSON structure or leave it to the SQL Server SELECT statement to determine the structure of the output.
There are two modes available with the FOR JSON Clause:
- Path mode: JSON format of the output is mentioned by the programmer
- Auto mode: JSON format of the output is determined by SQL Server