Several of the SQLite JSON functions, such as json_extract() and json_array_length(), take a path argument. This uses custom syntax along the lines of $.a[2].b, as described in the documentation here.
The syntax is similar to that used by MySQL, which is documented here.
Today I figured out the full rules for the path syntax, based on this forum thread and some dives into the SQLite source code.
Paths must always start with a $, which represents the root of the JSON value.
This can be followed by .key or ."key" to navigate into object keys, and [0] to navigate into arrays.
The double quote syntax is useful if your key includes . characters.
Given this example document:
{
"creatures": [
{
"name": "Cleo",
"species": "dog"
},
{
"name": "Azi",
"species": "chicken",
"weight.lb": 1.6
},
]
}$.creatures returns the JSON array (demo)$.creatures[0].name returns Cleo (demo)$.creatures[1]."weight.lb" returns 1.6 (demo)You can also use # inside the [] array syntax to refer to the length of the array.
This means $.creatures[#] (demo) will return null - because array indexing is from 0 so using the length as an index returns the item that's just past the end.
But... you can apply a single integer subtraction operation to that # - so you can return the name of the last creature in the array using this:
$.creatures[#-1].name returns Azi (demo)Here's the commit that added that custom SQLite extension in 2019.
If your object key contains a " character you can't use the $."..." syntax to access it - but provided it does not also contain a . character you can escape it like this:
$.has\" quotes in it
For example (demo):
select json_extract('{
"has\" quotes in it": "hello"
}', '$.has\" quotes in it')Outputs hello.
The latest source code for the JSON module can be found in ext/misc/json.c - in particular the static JsonNode *jsonLookup(...) function.
The unit tests are really useful - those are spread across these six files:
[#] syntax.Created 2022-01-18T15:00:56-08:00, updated 2022-09-20T13:52:31-07:00 · History · Edit