json_extract() path syntax in SQLite

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.

Basic syntax for objects and arrays

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

#-1 to access arrays by index from the end

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:

Here's the commit that added that custom SQLite extension in 2019.

Keys containing a double quote

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.

Source code

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:

Created 2022-01-18T15:00:56-08:00, updated 2022-09-20T13:52:31-07:00 · History · Edit