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
    },
  ]
}

• $.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):

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:

• 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.

Related

• postgresql Using json_extract_path in PostgreSQL - 2021-04-13
• jq Flattening nested JSON objects with jq - 2021-03-11
• json JSON Pointer - 2022-11-14
• sqlite Returning related rows in a single SQL query using JSON - 2022-09-16
• sqlite Seeing which functions are unique to a specific SQLite / Datasette instance - 2022-08-23
• sqlite Tracking SQLite table history using a JSON audit log - 2024-02-26
• sqlite Comparing database rows before and after with SQLite JSON functions - 2022-12-14
• sqlite Subqueries in select expressions in SQLite - also window functions - 2023-02-08
• sqlite Null case comparisons in SQLite - 2020-04-21
• jq Extracting objects recursively with jq - 2021-07-24

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