JSON Functions
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.
This function tests whether a string contains valid JSON.
ISJSON ( expression )
Argument | Description |
|---|---|
Expression | The string to test. |
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. |
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
This example would return a 1 since the expression ('true') is valid JSON.
SELECT ISJSON('true')
This functions extracts a scalar value from a JSON string.
JSON_VALUE ( expression , path )
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 | A JSON path that specifies the property to extract.
If the format of path isn't valid, JSON_VALUE returns an error. |
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.
The following example extracts the value of the JSON property into a local variable.
SET @city = JSON_VALUE(@payload, '$.properties.city.value')
This function extracts an object or an array from a JSON string.
JSON_QUERY ( expression [ , path ] )
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 | 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. |
Returns a JSON fragment of type nvarchar(max). The collation of the returned value is the same as the collation of the input expression.
DECLARE @data NVARCHAR(4000);
SET @data = N'{
"employees":
[ {
"name":"Kevin",
"email":"[email protected]",
"age":42
}
]
}';
SELECT JSON_QUERY(@data, '$.employees[0]') AS 'Result';
It would return the following result:
This function updates the value of a property in a JSON string and returns the updated JSON string.
JSON_MODIFY ( expression , path , newValue )
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>
|
newValue | The new value for the property specified by path.
The new value must be a [n]varchar or text. |
Returns the updated value of expression as properly formatted JSON text.
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 modified 1mo ago