JSON Functions

1. Overview

This page details the available JSON functions in Cinchy.The JSON functions covered in this section are:

• ​ISJSON​

• ​JSON_VALUE ​

• ​JSON_QUERY​

• ​JSON_MODIFY​

These functions are not currently available in Postgres deployments.

ISJSON

This function tests whether a string contains valid JSON.

Syntax

ISJSON ( expression )  

Argument	Description
Expression	The string to test.

Return Value

Return Value	Description
1	Returned if the input is a valid JSON object or array.
0	Returned if the input is not a valid JSON object of array.
Null	Returned if the expression is null.

Example 1

This example will return all rows from the [Expression] column in the [Product].[Function Table] that contain valid JSON.

SELECT [Expression]
FROM [Product].[Function Table]
WHERE ISJSON = 1 

Example 2

This example would return a 1 since the expression ('true') is valid JSON.

SELECT ISJSON('true')

JSON_VALUE

This functions extracts a scalar value from a JSON string.

Syntax

JSON_VALUE ( expression , path )

Arguments

Argument	Description
Expression	An expression. Typically the name of a variable or a column that contains JSON text. If JSON_VALUE finds JSON that is not valid in expression before it finds the value identified by path, the function returns an error. If JSON_VALUE doesn't find the value identified by path, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in expression.
Path	A JSON path that specifies the property to extract. If the format of path isn't valid, JSON_VALUE returns an error.

Return Value

Returns a single text value of type nvarchar(4000). The collation of the returned value is the same as the collation of the input expression.

Example 1

The following example extracts the value of the JSON property into a local variable.

SET @city = JSON_VALUE(@payload, '$.properties.city.value')

JSON_QUERY

This function extracts an object or an array from a JSON string.

Syntax

JSON_QUERY ( expression [ , path ] )

Arguments

Argument	Description
Expression	An expression. Typically the name of a variable or a column that contains JSON text. If JSON_QUERY finds JSON that is not valid in expression before it finds the value identified by path, the function returns an error. If JSON_QUERY doesn't find the value identified by path, it scans the entire text and returns an error if it finds JSON that is not valid anywhere in expression.
Path	A JSON path that specifies the property to extract. The default value for path is '$'. As a result, if you don't provide a value for path, JSON_QUERY returns the input expression. It follows zero-based indexing. Using employees[0] will return the first value in our JSON. If the format of path isn't valid, JSON_QUERY returns an error.

Return Value

Returns a JSON fragment of type nvarchar(max). The collation of the returned value is the same as the collation of the input expression.

Example 1

DECLARE @data NVARCHAR(4000);
SET @data = N'{
"employees":
[      {
         "name":"Kevin",
         "email":"[e-mail]",
         "age":42
          
}
]
}';
SELECT JSON_QUERY(@data, '$.employees[0]') AS 'Result';

It would return the following result:

{
         "name":"Kevin",
         "email":"[e-mail]",
         "age":42
 }

JSON_MODIFY

This function updates the value of a property in a JSON string and returns the updated JSON string.

Syntax

JSON_MODIFY ( expression , path , newValue )

Arguments

Argument	Description
Expression	An expression. Typically the name of a variable or a column that contains JSON text. JSON_MODIFY returns an error if expression doesn't contain valid JSON.
Path	A JSON path that specifies the property to extract. It takes the following format: [append] [ lax | strict ] $.<json path> append Optional modifier that specifies that the new value should be appended to the array referenced by <json path>. lax Specifies that the property referenced by <json path> does not have to exist. If the property is not present, JSON_MODIFY tries to insert the new value on the specified path. Insertion may fail if the property can't be inserted on the path. If you don't specify lax or strict, lax is the default mode. strict Specifies that the property referenced by <json path> must be in the JSON expression. If the property is not present, JSON_MODIFY returns an error. <json path> Specifies the path for the property to update. For more info, see JSON Path Expressions (SQL Server). JSON_MODIFY returns an error if the format of path isn't valid.
newValue	The new value for the property specified by path. The new value must be a [n]varchar or text.

Return Value

Returns the updated value of expression as properly formatted JSON text.

Example 1

The following example sets the surname to Smith.

SET @info=JSON_MODIFY(@info,'$.surname','Smith')

It would return the following formatted JSON text:

{
    "surname": "Smith"
}