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 )  
ArgumentDescription

Expression

The string to test.

Return Value

Return ValueDescription

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

ArgumentDescription

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

ArgumentDescription

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

ArgumentDescription

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

Last updated