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​
• OPENJSON

caution

These functions aren't currently available in PostGres deployments.

ISJSON

This function tests whether a string contains valid JSON.

Syntax

ISJSON ( expression )

Arguments

Argument	Description	Example
expression	The string to test	false

Return Type

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

The below example tests whether the expression true is valid JSON.

SELECT ISJSON('true')

Example 2

The below 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

JSON_VALUE

JSON_VALUE extracts a scalar value from a JSON string.

Syntax

JSON_VALUE ( expression , path )

Return Type

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.

Arguments

Argument	Description	Example
expression	An expression, typically a variable or column with JSON text.	@payload
path	A JSON path specifying the property to extract. Returns an error if the format of path isn't valid.	'$.properties.city.value'

Example

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

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

JSON_QUERY

JSON_QUERY extracts an object or an array from a JSON string.

Syntax

JSON_QUERY ( expression [ , path ] )

Return Type

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

Arguments

Argument	Description
Expression	An expression, usually a variable or column with JSON text. Returns an error for invalid JSON before or after identifying the value by path in JSON_QUERY.
Path	A JSON path for property extraction. Default value is '$', making JSON_QUERY return the input expression if path is not specified. Follows zero-based indexing (e.g., employees[0] for first value). Returns an error for invalid path format.

Example

DECLARE @data NVARCHAR(4000);
SET @data = N'{
"employees":
[      {
         "name":"Kevin",
         "email":"kevin@gmail.com",
         "age":42

}
]
}';
SELECT JSON_QUERY(@data, '$.employees[0]') AS 'Result';

It would return the following result:

{
         "name":"Kevin",
         "email":"kevin@gmail.com",
         "age":42
 }

JSON_MODIFY

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

Syntax

JSON_MODIFY ( expression , path , newValue )

Return Type

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

Arguments

Argument	Description
Expression	An expression, usually a variable or column with JSON text. JSON_MODIFY returns an error for invalid JSON in expression.
Path	A JSON path for extraction. Supports optional modifiers: append, lax, strict. append adds the new value to an array. lax tries to insert if property is missing. strict returns an error if property is missing. Invalid path format also triggers an error.
newValue	New value for property specified by path. Must be [n]varchar or 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"
}

OPENJSON

OPENJSON parses JSON text and returns objects and properties from the JSON input as rows and columns. In other words, OPENJSON provides a rowset view over a JSON document. 

Syntax

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

Return Value

The return value of this function depends on the WITH clause. The full list of possible values can be found here.

Arguments

Argument	Description	Example
jsonExpression	A Unicode character expression containing JSON text.
Path	Optional. A JSON path referencing an object or array within the jsonExpression. OPENJSON seeks into the JSON text at the specified position and parses only the referenced fragment.
with_clause	Optional. This clause explicitly defines the output schema for the OPENHSON function to return. You can review the elements of the clause here.

Example

SELECT o.*
FROM OPENJSON((
        SELECT j.Json
        FROM [Cinchy].[Tables] j
        WHERE
          j.[Deleted] IS NULL
          AND j.[Cinchy Id] = 1
        )) o