Functions for JSON
JSON is a lightweight data-interchange format. In YQL, it's represented by the Json
type. Unlike relational tables, JSON can store data with no schema defined. Here is an example of a valid JSON object:
[
{
"name": "Jim Holden",
"age": 30
},
{
"name": "Naomi Nagata",
"age": "twenty years old"
}
]
Despite the fact that the age
field in the first object is of the Number
type ("age": 21
) and in the second object its type is String
("age": "twenty years old"
), this is a fully valid JSON object.
To work with JSON, YQL implements a subset of the SQL support for JavaScript Object Notation (JSON) standard, which is part of the common ANSI SQL standard.
JsonPath
Values inside JSON objects are accessed using a query language called JsonPath. All functions for JSON accept a JsonPath query as an argument.
Let's look at an example. Suppose we have a JSON object like:
{
"comments": [
{
"id": 123,
"text": "A whisper will do, if it's all that you can manage."
},
{
"id": 456,
"text": "My life has become a single, ongoing revelation that I haven’t been cynical enough."
}
]
}
Then, to get the text of the second comment, we can write the following JsonPath query:
$.comments[1].text
In this query:
$
is a way to access the entire JSON object.$.comments
accesses thecomments
key of the JSON object.$.comments[1]
accesses the second element of the JSON array (element numbering starts from 0).$.comments[1].text
accesses thetext
key of the JSON object.- Query execution result:
"My life has become a single, ongoing revelation that I haven’t been cynical enough."
Quick reference
Operation | Example |
---|---|
Retrieving a JSON object key | $.key |
Retrieving all JSON object keys | $.* |
Accessing an array element | $[25] |
Retrieving an array subsegment | $[2 to 5] |
Accessing the last array element | $[last] |
Accessing all array elements | $[*] |
Unary operations | - 1 |
Binary operations | (12 * 3) % 4 + 8 |
Accessing a variable | $variable |
Logical operations | `(1 > 2) || (3 <= 4) && ("string" == "another")| |
Matching a regular expression | $.name like_regex "^[A-Za-z]+$" |
Checking the string prefix | $.name starts with "Bobbie" |
Checking if a path exists | exists ($.profile.name) |
Checking a Boolean expression for null | ($.age > 20) is unknown |
Filtering values | $.friends ? (@.age >= 18 && @.gender == "male") |
Getting the value type | $.name.type() |
Getting the array size | $.friends.size() |
Converting a string to a number | $.number.double() |
Rounding up a number | $.number.ceiling() |
Rounding down a number | $.number.floor() |
Returning the absolute value | $.number.abs() |
Getting key-value pairs from an object | $.profile.keyvalue() |
Data model
The result of executing all JsonPath expressions is a sequence of JSON values. For example:
- The result of executing the
"Bobbie"
expression is a sequence with the only element"Bobbie"
. Its length is 1. - The result of executing the
$
expression (that takes the entire JSON object) in JSON[1, 2, 3]
is[1, 2, 3]
. A sequence of 1 element of the array[1, 2, 3]
- The result of executing the
$[*]
expression (retrieving all array elements) in JSON[1, 2, 3]
is1, 2, 3
. A sequence of three items:1
,2
, and3
If the input sequence consists of multiple values, some operations are performed for each element (for example, accessing a JSON object key). However, other operations require a sequence of one element as input (for example, binary arithmetic operations).
The behavior of a specific operation is described in the corresponding section of the documentation.
Execution mode
JsonPath supports two execution modes, lax
and strict
. Setting the mode is optional. By default, lax
. The mode is specified at the beginning of a query. For example, strict $.key
.
The behavior for each mode is described in the corresponding sections with JsonPath operations.
Auto unpacking of arrays
When accessing a JSON object key in lax
mode, arrays are automatically unpacked.
Example
[
{
"key": 123
},
{
"key": 456
}
]
The lax $.key
query is successful and returns 123, 456
. As $
is an array, it's automatically unpacked and accessing the key of the $.key
JSON object is executed for each element in the array.
The strict $.key
query returns an error. In strict
mode, there is no support for auto unpacking of arrays. Since $
is an array and not an object, accessing the $.key
object key is impossible. You can fix this by writing strict $[*].key
.
Unpacking is only 1 level deep. In the event of nested arrays, only the outermost one is unpacked.
Wrapping values in arrays
When accessing an array element in lax
mode, JSON values are automatically wrapped in an array.
Example
{
"name": "Avasarala"
}
The lax $[0].name
query is successful and returns "Avasarala"
. As $
isn't an array, it's automatically wrapped in an array of length 1. Accessing the first element $[0]
returns the source JSON object where the name
key is taken.
The strict $[0].name
query returns an error. In strict
mode, values aren't wrapped in an array automatically. Since $
is an object and not an array, accessing the $[0]
element is impossible. You can fix this by writing strict $.name
.
Handling errors
Some errors are converted to an empty result when a query is executed in lax
mode.
Literals
Values of some types can be specified in a JsonPath query using literals:
Type | Example |
---|---|
Numbers | 42 , -1.23e-5 |
Boolean values | false , true |
Null | Null |
Stings | "Belt" |
Accessing JSON object keys
JsonPath supports accessing JSON object keys, such as $.session.user.name
.
Note
Accessing keys without quotes is only supported for keys that start with an English letter or underscore and only contain English letters, underscores, numbers, and a dollar sign. Use quotes for all other keys. For example, $.profile."this string has spaces"
or $.user."42 is the answer"
For each value from the input sequence:
- If the value is an array, it's automatically unpacked in
lax
mode. - If the value isn't a JSON object or if it is and the specified key is missing from this JSON object, a query executed in
strict
mode fails. Inlax
mode, an empty result is returned for this value.
The expression execution result is the concatenation of the results for each value from the input sequence.
Example
{
"name": "Amos",
"friends": [
{
"name": "Jim"
},
{
"name": "Alex"
}
]
}
lax |
strict |
|
---|---|---|
$.name |
"Amos" |
"Amos" |
$.surname |
Empty result | Error |
$.friends.name |
"Jim", "Alex" |
Error |
Accessing all JSON object keys
JsonPath supports accessing all JSON object keys at once: $.*
.
For each value from the input sequence:
- If the value is an array, it's automatically unpacked in
lax
mode. - If the value isn't a JSON object, a query executed in
strict
mode fails. Inlax
mode, an empty result is returned for this value.
The expression execution result is the concatenation of the results for each value from the input sequence.
Example
{
"profile": {
"id": 123,
"name": "Amos"
},
"friends": [
{
"name": "Jim"
},
{
"name": "Alex"
}
]
}
lax |
strict |
|
---|---|---|
$.profile.* |
123, "Amos" |
123, "Amos" |
$.friends.* |
"Jim", "Alex" |
Error |
Accessing an array element
JsonPath supports accessing array elements: $.friends[1, 3 to last - 1]
.
For each value from the input sequence:
- If the value isn't an array, a query executed in
strict
mode fails. Inlax
mode, values are automatically wrapped in an array. - The
last
keyword is replaced with the array's last index. Usinglast
outside of accessing the array is an error in both modes. - The specified indexes are calculated. Each of them must be a single number, otherwise the query fails in both modes.
- If the index is a fractional number, it's rounded down.
- If the index goes beyond the array boundaries, the query executed in
strict
mode fails. Inlax
mode, this index is ignored. - If a segment is specified and its start index is greater than the end index (for example,
$[20 to 1]
), the query fails instrict
mode. Inlax
mode, this segment is ignored. - All elements by the specified indexes are added to the result. Segments include both ends.
Examples
[
{
"name": "Camina",
"surname": "Drummer"
},
{
"name": "Josephus",
"surname": "Miller"
},
{
"name": "Bobbie",
"surname": "Draper"
},
{
"name": "Julie",
"surname": "Mao"
}
]
lax |
strict |
|
---|---|---|
$[0].name |
"Camina" |
"Camina" |
$[1, 2 to 3].name |
"Josephus", "Bobbie", "Julie" |
"Josephus", "Bobbie", "Julie" |
$[last - 2].name |
"Josephus" |
"Josephus" |
$[2, last + 200 to 50].name |
"Bobbie" |
Error |
$[50].name |
Empty result | Error |
Accessing all array elements
JsonPath supports accessing all array elements at once: $[*]
.
For each value from the input sequence:
- If the value isn't an array, a query executed in
strict
mode fails. Inlax
mode, values are automatically wrapped in an array. - All elements of the current array are added to the result.
Examples
[
{
"class": "Station",
"title": "Medina"
},
{
"class": "Corvette",
"title": "Rocinante"
}
]
lax |
strict |
|
---|---|---|
$[*].title |
"Medina", "Rocinante" |
"Medina", "Rocinante" |
lax $[0][*].class |
"Station" |
Error |
Let's analyze the last example step by step:
$[0]
returns the first element of the array, that is{"class": "Station", "title": "Medina"}
$[0][*]
expects an array for input, but an object was input instead. It's automatically wrapped in an array as[ {"class": "Station", "title": "Medina"} ]
- Now,
$[0][*]
can be executed and returns all elements of the array, that is{"class": "Station", "title": "Medina"}
$[0][*].class
returns theclass
field value:"Station"
.
Arithmetic operations
Note
All arithmetic operations work with numbers as with Double. Calculations are made with potential loss of accuracy.
Unary operations
JsonPath supports unary +
and -
.
A unary operation applies to all values from the input sequence. If a unary operation's input is a value that isn't a number, a query fails in both modes.
Example
[1, 2, 3, 4]
The strict -$[*]
query is successful and returns -1, -2, -3, -4
.
The lax -$
query fails as $
is an array and not a number.
Binary operations
JsonPath supports binary arithmetic operations (in descending order of priority):
- Multiplication
*
, dividing floating-point numbers/
, and taking the remainder%
(works as theMOD
function inSQL
). - Addition
+
, subtraction-
.
You can change the order of operations using parentheses.
If each argument of a binary operation is not a single number or a number is divided by 0, the query fails in both modes.
Examples
(1 + 2) * 3
returns9
1 / 2
returns0.5
5 % 2
returns1
1 / 0
fails- If JSON is
[-32.4, 5.2]
, the$[0] % $[1]
query returns-1.2
- If JSON is
[1, 2, 3, 4]
, thelax $[*] + $[*]
query fails as the$[*]
expression execution result is1, 2, 3, 4
, that is multiple numbers. A binary operation only requires one number for each of its arguments.
Boolean values
Unlike some other programming languages, Boolean values in JsonPath are not only true
and false
, but also null
(uncertainty).
JsonPath considers any values received from a JSON document to be non-Boolean. For example, a query like ! $.is_valid_user
(a logical negation applied to the is_valid_user
) field is syntactically invalid because the is_valid_user
field value is not Boolean (even when it actually stores true
or false
). The correct way to write this kind of query is to explicitly use a comparison with a Boolean value, such as $.is_valid_user == false
.
Logical operations
JsonPath supports some logical operations for Boolean values.
The arguments of any logical operation must be a single Boolean value.
All logical operations return a Boolean value.
Logical negation, !
Truth table:
x |
!x |
---|---|
true |
false |
false |
true |
Null |
Null |
Logical AND, &&
In the truth table, the first column is the left argument, the first row is the right argument, and each cell is the result of using the Logical AND both with the left and right arguments:
&& |
true |
false |
Null |
---|---|---|---|
true |
true |
false |
Null |
false |
false |
false |
false |
Null |
Null |
false |
Null |
Logical OR, ||
In the truth table, the first column is the left argument, the first row is the right argument, and each cell is the result of using the logical OR with both the left and right arguments:
|| |
true |
false |
Null |
---|---|---|---|
true |
true |
true |
true |
false |
true |
false |
Null |
Null |
true |
Null |
Null |
Examples
! (true == true)
, the result isfalse
(true == true) && (true == false)
, the result isfalse
(true == true) || (true == false)
, the result istrue
Comparison operators
JsonPath implements comparison operators for values:
- Equality,
==
- Inequality,
!=
and<>
- Less than and less than or equal to,
<
and=
- Greater than and greater than or equal to,
>
and>=
All comparison operators return a Boolean value. Both operator arguments support multiple values.
If an error occurs when calculating the operator arguments, it returns null
. In this case, the JsonPath query execution continues.
The arrays of each of the arguments are automatically unpacked. After that, for each pair where the first element is taken from the sequence of the left argument and the second one from the sequence of the right argument:
- The elements of the pair are compared
- If an error occurs during the comparison, the
ERROR
flag is set. - If the comparison result is true, the flag set is
FOUND
- If either the
ERROR
orFOUND
flag is set and the query is executed inlax
mode, no more pairs are analyzed.
If the pair analysis results in:
- The
ERROR
flag is set, the operator returnsnull
- The
FOUND
flag is set, the operator returnstrue
- Otherwise, it returns
false
We can say that this algorithm considers all pairs from the Cartesian product of the left and right arguments, trying to find the pair whose comparison returns true.
Elements in a pair are compared according to the following rules:
- If the left or right argument is an array or object, the comparison fails.
null == null
returns true- In all other cases, if one of the arguments is
null
, false is returned. - If the left and right arguments are of different types, the comparison fails.
- Strings are compared byte by byte.
true
is considered greater thanfalse
- Numbers are compared with the accuracy of
1e-20
Example
Let's take a JSON document as an example
{
"left": [1, 2],
"right": [4, "Inaros"]
}
and analyze the steps for executing the lax $.left < $.right
query:
- Auto unpacking of arrays in the left and right arguments. As a result, the left argument is the sequence
1, 2
and the right argument is4, "Iranos"
- Let's take the pair
(1, 4)
. The comparison is successful as1 < 4
is true. Set the flagFOUND
- Since the query is executed in
lax
mode and theFOUND
flag is set, we aren't analyzing any more pairs. - Since we have the
FOUND
flag set, the operator returns true.
Let's take the same query in a different execution mode: strict $.left < $.right
:
- Auto unpacking of arrays in the left and right arguments. As a result, the left argument is the sequence
1, 2
and the right argument is4, "Iranos"
- Let's take the pair
(1, 4)
. The comparison is successful as1 < 4
is true. Set the flagFOUND
- Let's take the pair
(2, 4)
. The comparison is successful as2 < 4
is true. Set the flagFOUND
- Let's take the pair
(1, "Iranos")
. The comparison fails as a number can't be compared with a string. Set the flagERROR
- Let's take the pair
(2, "Iranos")
. The comparison fails as a number can't be compared with a string. Set the flagERROR
- Since we have the
ERROR
flag set, the operator returnsnull
Predicates
JsonPath supports predicates which are expressions that return a Boolean value and check a certain condition. You can use them, for example, in filters.
like_regex
The like_regex
predicate lets you check if a string matches a regular expression. The syntax of regular expressions is the same as in Hyperscan UDF and REGEXP.
Syntax
<expression> like_regex <regexp string> [flag <flag string>]
Where:
<expression>
is a JsonPath expression with strings to be checked for matching the regular expression.<regexp string>
is a string with the regular expression.flag <flag string>
is an optional section where<flag string>
is a string with regular expression execution flags.
Supported flags:
i
: Disable the case sensitivity.
Execution
Before the check, the input sequence arrays are automatically unpacked.
After that, for each element of the input sequence:
- A check is made to find out if a string matches a regular expression.
- If the element isn't a string, the
ERROR
flag is set. - If the check result is true, the
FOUND
flag is set. - If either the
ERROR
orFOUND
flag is set and the query is executed inlax
mode, no more pairs are analyzed.
If the pair analysis results in:
- Setting the
ERROR
flag, the predicate returnsnull
- Setting the
FOUND
flag, the predicate returnstrue
- Otherwise, the predicate returns
false
Examples
"123456" like_regex "^[0-9]+$"
returnstrue
"123abcd456" like_regex "^[0-9]+$"
returnsfalse
"Naomi Nagata" like_regex "nag"
returnsfalse
"Naomi Nagata" like_regex "nag" flag "i"
returnstrue
starts with
The starts with
predicate lets you check if one string is a prefix of another.
Syntax
<string expression> starts with <prefix expression>
Where:
<string expression>
is a JsonPath expression with the string to check.<prefix expression>
is a JsonPath expression with a prefix string.
This means that the predicate will check that the <string expression>
starts with the <prefix expression>
string.
Execution
The first argument of the predicate must be a single string.
The second argument of the predicate must be a sequence of (possibly, multiple) strings.
For each element in a sequence of prefix strings:
- A check is made for whether "an element is a prefix of an input string"
- If the element isn't a string, the
ERROR
flag is set. - If the check result is true, the
FOUND
flag is set. - If either the
ERROR
orFOUND
flag is set and the query is executed inlax
mode, no more pairs are analyzed.
If the pair analysis results in:
- Setting the
ERROR
flag, the predicate returnsnull
- Setting the
FOUND
flag, the predicate returnstrue
- Otherwise, the predicate returns
false
Examples
"James Holden" starts with "James"
returnstrue
"James Holden" starts with "Amos"
returnsfalse
exists
The exists
predicate lets you check whether a JsonPath expression returns at least one element.
Syntax
exists (<expression>)
Where <expression>
is the JsonPath expression to be checked. Parentheses around the expression are required.
Execution
- The passed JsonPath expression is executed
- If an error occurs, the predicate returns
null
- If an empty sequence is obtained as a result of the execution, the predicate returns
false
- Otherwise, the predicate returns
true
Examples
Let's take a JSON document:
{
"profile": {
"name": "Josephus",
"surname": "Miller"
}
}
exists ($.profile.name)
returnstrue
exists ($.friends.profile.name)
returnsfalse
strict exists ($.friends.profile.name)
returnsnull
, because accessing non-existent object keys instrict
mode is an error.
is unknown
The is unknown
predicate lets you check if a Boolean value is null
.
Syntax
(<expression>) is unknown
Where <expression>
is the JsonPath expression to be checked. Only expressions that return a Boolean value are allowed. Parentheses around the expression are required.
Execution
- If the passed expression returns
null
, the predicate returnstrue
- Otherwise, the predicate returns
false
Examples
(1 == 2) is unknown
returnsfalse
. The1 == 2
expression returnedfalse
, which is notnull
(1 == "string") is unknown
returnstrue
. The1 == "string"
expression returnednull
, because strings and numbers can't be compared in JsonPath.
Filters
JsonPath lets you filter values obtained during query execution.
An expression in a filter must return a Boolean value.
Before filtering, the input sequence arrays are automatically unpacked.
For each element of the input sequence:
- The value of the current filtered
@
object becomes equal to the current element of the input sequence. - Executing the expression in the filter
- If an error occurs during the expression execution, the current element of the input sequence is skipped.
- If the expression execution result is the only
true
value, the current element is added to the filter result.
Example
Suppose we have a JSON document describing the user's friends
{
"friends": [
{
"name": "James Holden",
"age": 35,
"money": 500
},
{
"name": "Naomi Nagata",
"age": 30,
"money": 345
}
]
}
and we want to get a list of friends who are over 32 years old using a JsonPath query. To do this, you can write the following query:
$.friends ? (@.age > 32)
Let's analyze the query in parts:
$.friends
accesses thefriends
array in the JSON document.? ( ... )
is the filter syntax. An expression inside the parentheses is called a predicate.- `` accesses the currently filtered object. In our example, it's the object describing a friend of the user.
.age
accesses theage
field of the currently filtered object..age > 32
compares theage
field with the value 32. As a result of the query, only the values for which this predicate returned true remain.
The query only returns the first friend from the array of user's friends.
Like many other JsonPath operators, filters can be arranged in chains. Let's take a more complex query that selects the names of friends who are older than 20 and have less than 400 currency units:
$.friends ? (@.age > 20) ? (@.money < 400) . name
Let's analyze the query in parts:
$.friends
accesses thefriends
array in the JSON document.? (@.age > 20)
is the first filter. Since all friends are over 20, it just returns all the elements of thefriends
array.? (@.money < 400)
is the second filter. It only returns the second element of thefriends
array, since only itsmoney
field value is less than 400..name
accesses thename
field of filtered objects.
The query returns a sequence of a single element: "Naomi Nagata"
.
In practice, it's recommended to combine multiple filters into one if possible. The above query is equivalent to $.friends ? (@.age > 20 && @.money < 400) . name
.
Methods
JsonPath supports methods that are functions converting one sequence of values to another. The syntax for calling a method is similar to accessing the object key:
$.friends.size()
Just like in the case of accessing object keys, method calls can be arranged in chains:
$.numbers.double().floor()
type
The type
method returns a string with the type of the passed value.
For each element of the input sequence, the method adds this string to the output sequence according to the table below:
Value type | String with type |
---|---|
Null | "null" |
Boolean value | "boolean" |
Number | "number" |
String | "string" |
Array | "array" |
Object | "object" |
Examples
"Naomi".type()
returns"string"
false.type()
returns"boolean"
size
The size
method returns the size of the array.
For each element of the input sequence, the method adds the following to the output sequence:
- The size of the array if the element type is an array.
- For all other types (including objects), it adds
1
Examples
Let's take a JSON document:
{
"array": [1, 2, 3],
"object": {
"a": 1,
"b": 2
},
"scalar": "string"
}
And queries to it:
$.array.size()
returns3
$.object.size()
returns1
$.scalar.size()
returns1
Double
The double
method converts strings to numbers.
Before its execution, the input sequence arrays are automatically unpacked.
All elements in the input sequence must be strings that contain decimal numbers. It's allowed to specify the fractional part and exponent.
Examples
"125".double()
returns125
"125.456".double()
returns125.456
"125.456e-3".double()
returns0.125456
ceiling
The ceiling
method rounds up a number.
Before its execution, the input sequence arrays are automatically unpacked.
All elements in the input sequence must be numbers.
Examples
(1.3).ceiling()
returns2
(1.8).ceiling()
returns2
(1.5).ceiling()
returns2
(1.0).ceiling()
returns1
floor
The floor
method rounds down a number.
Before its execution, the input sequence arrays are automatically unpacked.
All elements in the input sequence must be numbers.
Examples
(1.3).floor()
returns1
(1.8).floor()
returns1
(1.5).floor()
returns1
(1.0).floor()
returns1
abs
The abs
method calculates the absolute value of a number (removes the sign).
Before its execution, the input sequence arrays are automatically unpacked.
All elements in the input sequence must be numbers.
Examples
(0.0).abs()
returns0
(1.0).abs()
returns1
(-1.0).abs()
returns1
keyvalue
The keyvalue
method converts an object to a sequence of key-value pairs.
Before its execution, the input sequence arrays are automatically unpacked.
All elements in the input sequence must be objects.
For each element of the input sequence:
- Each key-value pair in the element is analyzed.
- For each key-value pair, an object is generated with the keys
name
andvalue
. name
stores a string with the name of the key from the pair.value
stores the value from the pair.- All objects for this element are added to the output sequence.
Examples
Let's take a JSON document:
{
"name": "Chrisjen",
"surname": "Avasarala",
"age": 70
}
The $.keyvalue()
query returns the following sequence for it:
{
"name": "age",
"value": 70
},
{
"name": "name",
"value": "Chrisjen"
},
{
"name": "surname",
"value": "Avasarala"
}
Variables
Functions using JsonPath can pass values into a query. They are called variables. To access a variable, write the $
character and the variable name: $variable
.
Example
Let the planet
variable be equal to
{
"name": "Mars",
"gravity": 0.376
}
Then the strict $planet.name
query returns "Mars"
.
Unlike many programming languages, JsonPath doesn't support creating new variables or modifying existing ones.
Common arguments
All functions for JSON accept:
- A JSON value (can be an arbitrary
Json
orJson?
expression) - A JsonPath query (must be explicitly specified with a string literal)
- (Optional)
PASSING
section
PASSING section
Lets you pass values to a JsonPath query as variables.
Syntax
PASSING
<expression 1> AS <variable name 1>,
<expression 2> AS <variable name 2>,
...
<expression>
can have the following types:
- Numbers,
Date
,DateTime
, andTimestamp
(aCAST
intoDouble
will be made before passing a value to JsonPath) Utf8
,Bool
, andJson
You can set a <variable name>
in several ways:
- As an SQL name like
variable
- In quotes, for example,
"variable"
Example
JSON_VALUE(
$json,
"$.timestamp - $Now + $Hour"
PASSING
24 * 60 as Hour,
CurrentUtcTimestamp() as "Now"
)
JSON_EXISTS
The JSON_EXISTS
function checks if a JSON value meets the specified JsonPath.
Syntax
JSON_EXISTS(
<JSON expression>,
<JsonPath query>,
[<PASSING clause>]
[{TRUE | FALSE | UNKNOWN | ERROR} ON ERROR]
)
Return value: Bool?
Default value: If the ON ERROR
section isn't specified, the used section is FALSE ON ERROR
Behavior
-
If
<JSON expression>
isNULL
or an emptyJson?
, it returns an emptyBool?
-
If an error occurs during JsonPath execution, the returned value depends on the
ON ERROR
section:TRUE
: ReturnTrue
FALSE
: ReturnFalse
UNKNOWN
: Return an emptyBool?
ERROR
: Abort the entire query
-
If the result of JsonPath execution is one or more values, the return value is
True
. -
Otherwise,
False
is returned.
Examples
$json = CAST(@@{
"title": "Rocinante",
"crew": [
"James Holden",
"Naomi Nagata",
"Alex Kamai",
"Amos Burton"
]
}@@ as Json);
SELECT
JSON_EXISTS($json, "$.title"), -- True
JSON_EXISTS($json, "$.crew[*]"), -- True
JSON_EXISTS($json, "$.nonexistent"); -- False, as JsonPath returns an empty result
SELECT
-- JsonPath error, False is returned because the default section used is FALSE ON ERROR
JSON_EXISTS($json, "strict $.nonexistent");
SELECT
-- JsonPath error, the entire YQL query fails.
JSON_EXISTS($json, "strict $.nonexistent" ERROR ON ERROR);
JSON_VALUE
The JSON_VALUE
function retrieves a scalar value from JSON (anything that isn't an array or object).
Syntax
JSON_VALUE(
<JSON expression>,
<JsonPath query>,
[<PASSING clause>]
[RETURNING <type>]
[{ERROR | NULL | DEFAULT <expr>} ON EMPTY]
[{ERROR | NULL | DEFAULT <expr>} ON ERROR]
)
Return value: <type>?
Default values:
- If the
ON EMPTY
section isn't specified, the section used isNULL ON EMPTY
- If the
ON ERROR
section isn't specified, the section used isNULL ON ERROR
- If the
RETURNING
section isn't specified, then for<type>
, the type used isUtf8
Behavior:
-
If
<JSON expression>
isNULL
or an emptyJson?
, it returns an empty<type>?
-
If an error occurs, the returned value depends on the
ON ERROR
section:NULL
: Return an empty<type>?
ERROR
: Abort the entire queryDEFAULT <expr>
: Return<expr>
after running theCAST
function to convert the data type to<type>?
. If theCAST
fails, the entire query fails, too.
-
If the JsonPath execution result is empty, the returned value depends on the
ON EMPTY
section:NULL
: Return an empty<type>?
ERROR
: Abort the entire queryDEFAULT <expr>
: Return<expr>
after running theCAST
function to convert the data type to<type>?
. If theCAST
fails, the behavior matches theON ERROR
section.
-
If the result of JsonPath execution is a single value, then:
- If the
RETURNING
section isn't specified, the value is converted toUtf8
. - Otherwise, the
CAST
function is run to convert the value to<type>
. If theCAST
fails, the behavior matches theON ERROR
section. In this case, the value from JSON must match the<type>
type.
- If the
-
Return the result
Correlation between JSON and YQL types:
- JSON Number: Numeric types,
Date
,DateTime
, andTimestamp
- JSON Bool:
Bool
- JSON String:
Utf8
andString
Errors executing JSON_VALUE
are as follows:
- Errors evaluating JsonPath
- The result of JsonPath execution is a number of values or a non-scalar value.
- The type of value returned by JSON doesn't match the expected one.
The RETURNING
section supports such types as numbers, Date
, DateTime
, Timestamp
, Utf8
, String
, and Bool
.
Examples
$json = CAST(@@{
"friends": [
{
"name": "James Holden",
"age": 35
},
{
"name": "Naomi Nagata",
"age": 30
}
]
}@@ as Json);
SELECT
JSON_VALUE($json, "$.friends[0].age"), -- "35" (type Utf8?)
JSON_VALUE($json, "$.friends[0].age" RETURNING Uint64), -- 35 (type Uint64?)
JSON_VALUE($json, "$.friends[0].age" RETURNING Utf8); -- an empty Utf8? due to an error. The JSON's Number type doesn't match the string Utf8 type.
SELECT
-- "empty" (type String?)
JSON_VALUE(
$json,
"$.friends[50].name"
RETURNING String
DEFAULT "empty" ON EMPTY
);
SELECT
-- 20 (type Uint64?). The result of JsonPath execution is empty, but the
-- default value from the ON EMPTY section can't be cast to Uint64.
-- That's why the value from ON ERROR is used.
JSON_VALUE(
$json,
"$.friends[50].age"
RETURNING Uint64
DEFAULT -1 ON EMPTY
DEFAULT 20 ON ERROR
);
JSON_QUERY
The JSON_QUERY
function lets you retrieve arrays and objects from JSON.
Syntax
JSON_QUERY(
<JSON expression>,
<JsonPath query>,
[<PASSING clause>]
[WITHOUT [ARRAY] | WITH [CONDITIONAL | UNCONDITIONAL] [ARRAY] WRAPPER]
[{ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT} ON EMPTY]
[{ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT} ON ERROR]
)
Return value: Json?
Default values:
- If the
ON EMPTY
section isn't specified, the section used isNULL ON EMPTY
- If the
ON ERROR
section isn't specified, the section used isNULL ON ERROR
- If the
WRAPPER
section isn't specified, the section used isWITHOUT WRAPPER
- If the
WITH WRAPPER
section is specified butCONDITIONAL
orUNCONDITIONAL
is omitted, then the section used isUNCONDITIONAL
Behavior:
Note
You can't specify the WITH ... WRAPPER
and ON EMPTY
sections at the same time.
-
If
<JSON expression>
isNULL
or an emptyJson?
, it returns an emptyJson?
-
If the
WRAPPER
section is specified, then:WITHOUT WRAPPER
orWITHOUT ARRAY WRAPPER
: Don't convert the result of JsonPath execution in any way.WITH UNCONDITIONAL WRAPPER
orWITH UNCONDITIONAL ARRAY WRAPPER
: Wrap the result of JsonPath execution in an array.WITH CONDITIONAL WRAPPER
orWITH CONDITIONAL ARRAY WRAPPER
: Wrap the result of JsonPath execution in an array if it isn't the only array or object.
-
If the JsonPath execution result is empty, the returned value depends on the
ON EMPTY
section:NULL
: Return an emptyJson?
ERROR
: Abort the entire queryEMPTY ARRAY
: Return an empty JSON array,[]
EMPTY OBJECT
: Return an empty JSON object,{}
-
If an error occurs, the returned value depends on the
ON ERROR
section:NULL
: Return an emptyJson?
ERROR
: Abort the entire queryEMPTY ARRAY
: Return an empty JSON array,[]
EMPTY OBJECT
: Return an empty JSON object,{}
-
Return the result
Errors running a JSON_QUERY
:
- Errors evaluating JsonPath
- The result of JsonPath execution is a number of values (even after using the
WRAPPER
section) or a scalar value.
Examples
$json = CAST(@@{
"friends": [
{
"name": "James Holden",
"age": 35
},
{
"name": "Naomi Nagata",
"age": 30
}
]
}@@ as Json);
SELECT
JSON_QUERY($json, "$.friends[0]"); -- {"name": "James Holden", "age": 35}
SELECT
JSON_QUERY($json, "$.friends.name" WITH UNCONDITIONAL WRAPPER); -- ["James Holden", "Naomi Nagata"]
SELECT
JSON_QUERY($json, "$.friends[0]" WITH CONDITIONAL WRAPPER), -- {"name": "James Holden", "age": 35}
JSON_QUERY($json, "$.friends.name" WITH CONDITIONAL WRAPPER); -- ["James Holden", "Naomi Nagata"]