Functions for JSON

• Cookbook
  • Accessing a field in the database
• JsonPath
  • Quick reference
  • Data model
  • Execution mode
  • Literals
  • Accessing JSON object keys
  • Accessing all JSON object keys
  • Accessing an array element
  • Accessing all array elements
  • Arithmetic operations
  • Boolean values
  • Logical operations
  • Comparison operators
  • Predicates
  • Filters
  • Methods
  • Variables
• Common arguments
  • PASSING section
• JSON_EXISTS
• JSON_VALUE
• JSON_QUERY

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:

1. $ is a way to access the entire JSON object.
2. $.comments accesses the comments key of the JSON object.
3. $.comments[1] accesses the second element of the JSON array (element numbering starts from 0).
4. $.comments[1].text accesses the text key of the JSON object.
5. Query execution result: "My life has become a single, ongoing revelation that I haven’t been cynical enough."

Quick reference

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:

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:

1. If the value is an array, it’s automatically unpacked in lax mode.
2. 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"
        }
    ]
}

Accessing all JSON object keys

JsonPath supports accessing all JSON object keys at once: $.*.

For each value from the input sequence:

1. If the value is an array, it’s automatically unpacked in lax mode.
2. 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"
        }
    ]
}

Accessing an array element

JsonPath supports accessing array elements: $.friends[1, 3 to last - 1].

For each value from the input sequence:

1. If the value isn’t an array, a query executed in strict mode fails. In lax mode, values are automatically wrapped in an array.
2. The last keyword is replaced with the array’s last index. Using last outside of accessing the array is an error in both modes.
3. The specified indexes are calculated. Each of them must be a single number, otherwise the query fails in both modes.
4. If the index is a fractional number, it’s rounded down.
5. If the index goes beyond the array boundaries, the query executed in strict mode fails. In lax mode, this index is ignored.
6. 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.
7. 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"
    }
]

Accessing all array elements

JsonPath supports accessing all array elements at once: $[*].

For each value from the input sequence:

1. If the value isn’t an array, a query executed in strict mode fails. In lax mode, values are automatically wrapped in an array.
2. All elements of the current array are added to the result.

Examples:

[
    {
        "class": "Station",
        "title": "Medina"
    },
    {
        "class": "Corvette",
        "title": "Rocinante"
    }
]

Let’s analyze the last example step by step:

1. $[0] returns the first element of the array, that is {"class": "Station", "title": "Medina"}
2. $[0][*] expects an array for input, but an object was input instead. It’s automatically wrapped in an array as [ {"class": "Station", "title": "Medina"} ]
3. Now, $[0][*] can be executed and returns all elements of the array, that is {"class": "Station", "title": "Medina"}
4. $[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):

1. Multiplication *, dividing floating-point numbers /, and taking the remainder % (works as the MOD function in SQL).
2. 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:

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:

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:

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:

1. The elements of the pair are compared
2. If an error occurs during the comparison, the ERROR flag is set.
3. If the comparison result is true, the flag set is FOUND
4. 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:

1. The ERROR flag is set, the operator returns null
2. The FOUND flag is set, the operator returns true
3. 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:

1. If the left or right argument is an array or object, the comparison fails.
2. null == null returns true
3. In all other cases, if one of the arguments is null, false is returned.
4. If the left and right arguments are of different types, the comparison fails.
5. Strings are compared byte by byte.
6. true is considered greater than false
7. 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:

1. 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"
2. Let’s take the pair (1, 4). The comparison is successful as 1 < 4 is true. Set the flag FOUND
3. Since the query is executed in lax mode and the FOUND flag is set, we aren’t analyzing any more pairs.
4. 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:

1. 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"
2. Let’s take the pair (1, 4). The comparison is successful as 1 < 4 is true. Set the flag FOUND
3. Let’s take the pair (2, 4). The comparison is successful as 2 < 4 is true. Set the flag FOUND
4. Let’s take the pair (1, "Iranos"). The comparison fails as a number can’t be compared with a string. Set the flag ERROR
5. Let’s take the pair (2, "Iranos"). The comparison fails as a number can’t be compared with a string. Set the flag ERROR
6. 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:

1. <expression> is a JsonPath expression with strings to be checked for matching the regular expression.
2. <regexp string> is a string with the regular expression.
3. 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:

1. A check is made to find out if a string matches a regular expression.
2. If the element isn’t a string, the ERROR flag is set.
3. If the check result is true, the FOUND flag is set.
4. 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:

1. Setting the ERROR flag, the predicate returns null
2. Setting the FOUND flag, the predicate returns true
3. Otherwise, the predicate returns false

Examples

1. "123456" like_regex "^[0-9]+$" returns true
2. "123abcd456" like_regex "^[0-9]+$" returns false
3. "Naomi Nagata" like_regex "nag" returns false
4. "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:

1. <string expression> is a JsonPath expression with the string to check.
2. <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:

1. A check is made for whether “an element is a prefix of an input string”
2. If the element isn’t a string, the ERROR flag is set.
3. If the check result is true, the FOUND flag is set.
4. 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:

1. Setting the ERROR flag, the predicate returns null
2. Setting the FOUND flag, the predicate returns true
3. Otherwise, the predicate returns false

Examples

1. "James Holden" starts with "James" returns true
2. "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

1. The passed JsonPath expression is executed
2. If an error occurs, the predicate returns null
3. If an empty sequence is obtained as a result of the execution, the predicate returns false
4. Otherwise, the predicate returns true

Examples

Let’s take a JSON document:

{
    "profile": {
        "name": "Josephus",
        "surname": "Miller"
    }
}

1. exists ($.profile.name) returns true
2. exists ($.friends.profile.name) returns false
3. 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

1. If the passed expression returns null, the predicate returns true
2. Otherwise, the predicate returns false

Examples

1. (1 == 2) is unknown returns false. The 1 == 2 expression returned false, which is not null
2. (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:

1. The value of the current filtered @ object becomes equal to the current element of the input sequence.
2. Executing the expression in the filter
3. If an error occurs during the expression execution, the current element of the input sequence is skipped.
4. 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:

Examples

1. "Naomi".type() returns "string"
2. 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:

1. The size of the array if the element type is an array.
2. 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:

1. $.array.size() returns 3
2. $.object.size() returns 1
3. $.scalar.size() returns 1

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

1. "125".double() returns 125
2. "125.456".double() returns 125.456
3. "125.456e-3".double() returns 0.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. (1.3).ceiling() returns 2
2. (1.8).ceiling() returns 2
3. (1.5).ceiling() returns 2
4. (1.0).ceiling() returns 1

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. (1.3).floor() returns 1
2. (1.8).floor() returns 1
3. (1.5).floor() returns 1
4. (1.0).floor() returns 1

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

1. (0.0).abs() returns 0
2. (1.0).abs() returns 1
3. (-1.0).abs() returns 1

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:

1. Each key-value pair in the element is analyzed.
2. For each key-value pair, an object is generated with the keys name and value.
3. name stores a string with the name of the key from the pair.
4. value stores the value from the pair.
5. 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:

1. A JSON value (can be an arbitrary Json or Json? expression)
2. A JsonPath query (must be explicitly specified with a string literal)
3. (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, and Timestamp (a CAST into Double will be made before passing a value to JsonPath)
• Utf8, Bool, and Json

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:

1. If <JSON expression> is NULL or an empty Json?, it returns an empty Bool?
2. If an error occurs during JsonPath execution, the returned value depends on the ON ERROR section:
   • TRUE: Return True
   • FALSE: Return False
   • UNKNOWN: Return an empty Bool?
   • ERROR: Abort the entire query
3. If the result of JsonPath execution is one or more values, the return value is True.
4. 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:

1. If the ON EMPTY section isn’t specified, the section used is NULL ON EMPTY
2. If the ON ERROR section isn’t specified, the section used is NULL ON ERROR
3. If the RETURNING section isn’t specified, then for <type>, the type used is Utf8

Behavior:

1. If <JSON expression> is NULL or an empty Json?, it returns an empty <type>?
2. If an error occurs, the returned value depends on the ON ERROR section:
   • NULL: Return an empty <type>?
   • ERROR: Abort the entire query
   • DEFAULT <expr>: Return <expr> after running the CAST function to convert the data type to <type>?. If the CAST fails, the entire query fails, too.
3. 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 query
   • DEFAULT <expr>: Return <expr> after running the CAST function to convert the data type to <type>?. If the CAST fails, the behavior matches the ON ERROR section.
4. If the result of JsonPath execution is a single value, then:
   • If the RETURNING section isn’t specified, the value is converted to Utf8.
   • Otherwise, the CAST function is run to convert the value to <type>. If the CAST fails, the behavior matches the ON ERROR section. In this case, the value from JSON must match the <type> type.
5. Return the result

Correlation between JSON and YQL types:

• JSON Number: Numeric types, Date, DateTime, and Timestamp
• JSON Bool: Bool
• JSON String: Utf8 and String

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:

1. If the ON EMPTY section isn’t specified, the section used is NULL ON EMPTY
2. If the ON ERROR section isn’t specified, the section used is NULL ON ERROR
3. If the WRAPPER section isn’t specified, the section used is WITHOUT WRAPPER
4. If the WITH WRAPPER section is specified but CONDITIONAL or UNCONDITIONAL is omitted, then the section used is UNCONDITIONAL

Behavior:

Note

You can’t specify the WITH ... WRAPPER and ON EMPTY sections at the same time.

1. If <JSON expression> is NULL or an empty Json?, it returns an empty Json?
2. If the WRAPPER section is specified, then:
   • WITHOUT WRAPPER or WITHOUT ARRAY WRAPPER: Don’t convert the result of JsonPath execution in any way.
   • WITH UNCONDITIONAL WRAPPER or WITH UNCONDITIONAL ARRAY WRAPPER: Wrap the result of JsonPath execution in an array.
   • WITH CONDITIONAL WRAPPER or WITH CONDITIONAL ARRAY WRAPPER: Wrap the result of JsonPath execution in an array if it isn’t the only array or object.
3. If the JsonPath execution result is empty, the returned value depends on the ON EMPTY section:
   • NULL: Return an empty Json?
   • ERROR: Abort the entire query
   • EMPTY ARRAY: Return an empty JSON array, []
   • EMPTY OBJECT: Return an empty JSON object, {}
4. If an error occurs, the returned value depends on the ON ERROR section:
   • NULL: Return an empty Json?
   • ERROR: Abort the entire query
   • EMPTY ARRAY: Return an empty JSON array, []
   • EMPTY OBJECT: Return an empty JSON object, {}
5. 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"]