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.

Cookbook

$json = CAST(@@{
    "friends": [
        {
            "name": "James Holden",
            "age": 35
        },
        {
            "name": "Naomi Nagata",
            "age": 30
        }
    ]
}@@ AS Json);

SELECT
    JSON_EXISTS($json, "$.friends[*].name"), -- True,
    JSON_VALUE($json, "$.friends[0].age"), -- "35" (type Utf8?)
    JSON_QUERY($json, "$.friends[0]"); -- {"name": "James Holden", "age": 35}

Accessing a field in the database

In tables, data can be stored in JSON format or as a string representation. JSON_* functions expect JSON as an input to run. To convert String->JSON, use the CAST function, such as CAST (my_string AS JSON).

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 the comments key of the JSON object.
$.comments[1] accesses the second element of the JSON array (element numbering starts from 0).
$.comments[1].text accesses the text 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] is 1, 2, 3. A sequence of three items:1, 2, and 3

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. In lax 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. In lax 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. In lax mode, values are automatically wrapped in an array.
The last keyword is replaced with the array's last index. Using last 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. In lax 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 in strict mode. In lax 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. In lax 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 the class 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 the MOD function in SQL).
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 returns 9
1 / 2 returns 0.5
5 % 2 returns 1
1 / 0 fails
If JSON is [-32.4, 5.2], the $[0] % $[1] query returns -1.2
If JSON is [1, 2, 3, 4], the lax $[*] + $[*] query fails as the $[*] expression execution result is 1, 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 is false
(true == true) && (true == false), the result is false
(true == true) || (true == false), the result is true

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 or FOUND flag is set and the query is executed in lax mode, no more pairs are analyzed.

If the pair analysis results in:

The ERROR flag is set, the operator returns null
The FOUND flag is set, the operator returns true
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 than false
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 is 4, "Iranos"
Let's take the pair (1, 4). The comparison is successful as 1 < 4 is true. Set the flag FOUND
Since the query is executed in lax mode and the FOUND 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 is 4, "Iranos"
Let's take the pair (1, 4). The comparison is successful as 1 < 4 is true. Set the flag FOUND
Let's take the pair (2, 4). The comparison is successful as 2 < 4 is true. Set the flag FOUND
Let's take the pair (1, "Iranos"). The comparison fails as a number can't be compared with a string. Set the flag ERROR
Let's take the pair (2, "Iranos"). The comparison fails as a number can't be compared with a string. Set the flag ERROR
Since we have the ERROR flag set, the operator returns null

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 or FOUND flag is set and the query is executed in lax mode, no more pairs are analyzed.

If the pair analysis results in:

Setting the ERROR flag, the predicate returns null
Setting the FOUND flag, the predicate returns true
Otherwise, the predicate returns false

Examples

"123456" like_regex "^[0-9]+$" returns true
"123abcd456" like_regex "^[0-9]+$" returns false
"Naomi Nagata" like_regex "nag" returns false
"Naomi Nagata" like_regex "nag" flag "i" returns true

`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 or FOUND flag is set and the query is executed in lax mode, no more pairs are analyzed.

If the pair analysis results in:

Setting the ERROR flag, the predicate returns null
Setting the FOUND flag, the predicate returns true
Otherwise, the predicate returns false

Examples

"James Holden" starts with "James" returns true
"James Holden" starts with "Amos" returns false

`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) returns true
exists ($.friends.profile.name) returns false
strict exists ($.friends.profile.name) returns null, because accessing non-existent object keys in strict 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 returns true
Otherwise, the predicate returns false

Examples

(1 == 2) is unknown returns false. The 1 == 2 expression returned false, which is not null
(1 == "string") is unknown returns true. The 1 == "string" expression returned null, 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 the friends 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 the age field of the currently filtered object.
.age > 32 compares the age 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 the friends array in the JSON document.
? (@.age > 20) is the first filter. Since all friends are over 20, it just returns all the elements of the friends array.
? (@.money < 400) is the second filter. It only returns the second element of the friends array, since only its money field value is less than 400.
.name accesses the name 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() returns 3
$.object.size() returns 1
$.scalar.size() returns 1