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.
This function tests whether a string contains valid JSON.
| Argument | Description |
|---|---|
| Return Value | Description |
|---|---|
This example will return all rows from the [Expression] column in the [Product].[Function Table] that contain valid JSON.
This example would return a 1 since the expression ('true') is valid JSON.
This functions extracts a scalar value from a JSON string.
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.
This function extracts an object or an array from a JSON string.
Returns a JSON fragment of type nvarchar(max). The collation of the returned value is the same as the collation of the input expression.
It would return the following result:
This function updates the value of a property in a JSON string and returns the updated JSON string.
Returns the updated value of expression as properly formatted JSON text.
The following example sets the surname to Smith.
It would return the following formatted JSON text:
| Argument | Description |
|---|---|
| Argument | Description |
|---|---|
| Argument | Description |
|---|---|
Expression
The string to test.
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.
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.
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.
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.