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:

1
{
2
  "creatures": [
3
    {
4
      "name": "Cleo",
5
      "species": "dog"
6
    },
7
    {
8
      "name": "Azi",
9
      "species": "chicken",
10
      "weight.lb": 1.6
11
    },
12
  ]
13
}

$.creatures returns the JSON array (demo)
$.creatures[0].name returns Cleo (demo)
$.creatures[1]."weight.lb" returns 1.6 (demo)

#-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:

$.creatures[#-1].name returns Azi (demo)

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):

1
select json_extract('{
2
    "has\" quotes in it": "hello"
3
}', '$.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:

test/json1.test
test/json101.test
test/json102.test
test/json103.test
test/json104.test
test/json105.test - this one has the tests for [#] syntax.