Accessing values inside JSON with YQL
YQL provides two main ways to retrieve values from JSON:
- Using JSON functions from the SQL standard. This approach is recommended for simple cases and for teams that are familiar with them from other DBMSs.
- Using Yson UDF, list and dict builtins, and lambdas. This approach is more flexible and tightly integrated with YDB's data type system, thus recommended for complex cases.
Below are the recipes that will use the same input JSON to demonstrate how to use each option to check whether a key exists, get a specific value, and retrieve a subtree.
JSON functions
$json = @@{
"friends": [
{
"name": "James Holden",
"age": 35
},
{
"name": "Naomi Nagata",
"age": 30
}
]
}@@j;
SELECT
JSON_EXISTS($json, "$.friends[*].name"), -- True
CAST(JSON_VALUE($json, "$.friends[0].age") AS Int32), -- 35
JSON_QUERY($json, "$.friends[0]"); -- {"name": "James Holden", "age": 35}
JSON_* functions expect the Json data type as an input to run. In this example, the string literal has the suffix j, marking it as Json. In tables, data could be stored in either JSON format or as a string representation. To convert data from String to JSON data type, use the CAST function, such as CAST(my_string AS JSON).
Yson UDF
This approach typically combines multiple functions and expressions, so a query might leverage different specific strategies.
Convert the whole JSON to YQL containers
$json = @@{
"friends": [
{
"name": "James Holden",
"age": 35
},
{
"name": "Naomi Nagata",
"age": 30
}
]
}@@j;
$containers = Yson::ConvertTo($json, Struct<friends:List<Struct<name:String?,age:Int32?>>>);
$has_name = ListAny(
ListMap($containers.friends, ($friend) -> {
return $friend.name IS NOT NULL;
})
);
$get_age = $containers.friends[0].age;
$get_first_friend = Yson::SerializeJson(Yson::From($containers.friends[0]));
SELECT
$has_name, -- True
$get_age, -- 35
$get_first_friend; -- {"name": "James Holden", "age": 35}
It is not necessary to convert the whole JSON object to a structured combination of containers. Some fields can be omitted if not used, while some subtrees could be left in an unstructured data type like Json.
Work with in-memory representation
$json = @@{
"friends": [
{
"name": "James Holden",
"age": 35
},
{
"name": "Naomi Nagata",
"age": 30
}
]
}@@j;
$has_name = ListAny(
ListMap(Yson::ConvertToList($json.friends), ($friend) -> {
return Yson::Contains($friend, "name");
})
);
$get_age = Yson::ConvertToInt64($json.friends[0].age);
$get_first_friend = Yson::SerializeJson($json.friends[0]);
SELECT
$has_name, -- True
$get_age, -- 35
$get_first_friend; -- {"name": "James Holden", "age": 35}