JSON functions

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 aren't 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 isn't 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's 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's 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's 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's 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
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"
}