JSON Functions

1. Overview

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

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

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

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

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

[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.

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