JSON functions

Overview

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

These functions aren't currently available in PostGres deployments.

ISJSON

This function tests whether a string contains valid JSON.

Syntax

ISJSON ( expression )  

Return Value

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

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

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":"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

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

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"
}

Last updated