JSON_
Represents the SQLite json_tree() table-valued function.
This function recursively walks through a JSON structure, returning one row for each element (object, array, or primitive value) encountered. This provides a "deep" or recursive iteration over the entire JSON document, unlike the shallow iteration of JSON_EACH.
JSON_TREE is useful for searching for values or keys anywhere within a complex, nested JSON object or array.
Example
val jsonDocument = """
{
"id": 1,
"user": {
"name": "Bob",
"details": {
"emails": ["bob@example.com", "b.work@example.com"]
}
}
}
"""
val jsonTree = JSON_TREE(JsonLiteral(jsonDocument))
// Find all email addresses in the document
select(jsonTree.value)
.from(jsonTree)
.where { jsonTree.value.LIKE("%@example.com") }
.execute()
.forEach {
println(it[jsonTree.value])
// Result "bob@example.com", "b.work@example.com"
}Example with table
object Products : KQLiteTable("product") {
val id = longColumn("id")
val properties = jsonColumn("properties") // Contains complex nested JSON
}
val jsonTree = JSON_TREE(Products.properties)
select(Products.id)
.from(Products, jsonTree)
.where {
jsonTree.value.LIKE("%color%")
}.execute()
.forEach {
println(it.getString(0))
}Author
MOHAMMAD AZIM ANSARI
Parameters
The column containing the JSON string to iterate over. This can be any column type, as it will be treated as text.
An optional JSON path to a specific object or array within the JSON string. If provided, json_tree will iterate over that nested element instead of the root.
See also
for a description of the output columns.
for a shallow iteration.