Hi, I think i've noticed a change in JSON path handling introduced between v3.44.2 and v3.45.0.

It looks like pre 3.45.0, 'a.b' could be used as a JSON path, but post 3.45.0 the path must be prefixed with $..

I'm using SQLite through a Javascript library called better-sqlite3. There is a chance that the issue may be with that wrapper.

test-version

yarn add "better-sqlite3@$1" >>yarn.log
node ./test.js
yarn remove "better-sqlite3" >>yarn.log

test.js

const Database = require('better-sqlite3');
const { version } = require('better-sqlite3/package');

const db = new Database();

const query = db.prepare(`
  SELECT value
       , value->>'a'
       , value->>'a.b'
       , value->>'a'->>'b'
       , value->'a'
       , value->'a.b'
       , value->'a'->'b'
       , value->>'$.a.b'
    FROM JSON_EACH('[{"a":{"b":1}}, {"a":{"b":7}}]')
`);

console.log(version.padStart(6, ' '), query.all())

Run like so:

./test-version 9.2.2 && ./test-version 9.3.0

result:

 9.2.2 [
  {
    value: '{"a":{"b":1}}',
    "value->>'a'": '{"b":1}',
    "value->>'a.b'": 1,
    "value->>'a'->>'b'": 1,
    "value->'a'": '{"b":1}',
    "value->'a.b'": '1',
    "value->'a'->'b'": '1',
    "value->>'$.a.b'": 1
  },
  {
    value: '{"a":{"b":7}}',
    "value->>'a'": '{"b":7}',
    "value->>'a.b'": 7,
    "value->>'a'->>'b'": 7,
    "value->'a'": '{"b":7}',
    "value->'a.b'": '7',
    "value->'a'->'b'": '7',
    "value->>'$.a.b'": 7
  }
]
 9.3.0 [
  {
    value: '{"a":{"b":1}}',
    "value->>'a'": '{"b":1}',
    "value->>'a.b'": null,
    "value->>'a'->>'b'": 1,
    "value->'a'": '{"b":1}',
    "value->'a.b'": null,
    "value->'a'->'b'": '1',
    "value->>'$.a.b'": 1
  },
  {
    value: '{"a":{"b":7}}',
    "value->>'a'": '{"b":7}',
    "value->>'a.b'": null,
    "value->>'a'->>'b'": 7,
    "value->'a'": '{"b":7}',
    "value->'a.b'": null,
    "value->'a'->'b'": '7',
    "value->>'$.a.b'": 7
  }
]

The better-sqlite3 versions can be tied to SQLite versions via their changelog:

• https://github.com/WiseLibs/better-sqlite3/releases/tag/v9.2.2
• https://github.com/WiseLibs/better-sqlite3/releases/tag/v9.3.0

I notice from the changelog for v3.45.0 that "All JSON functions are rewritten to use a new internal parse tree format", which I guess is related

I think what you have discovered is a bug-fix in the JSON path parser.

Good to know :¬)

Is there clearer definition of the way these paths work?

https://sqlite.org/json1.html#path_arguments reads:

A well-formed PATH is a text value that begins with exactly one '$' character followed by zero or more instances of ".objectlabel" or "[arrayindex]".

But in this case, I wouldn't expect most of my previous examples to work at all (as they're missing the leading $).

Also, what would be the correct way to include a . character in a JSON key?

E.g.

  SELECT value
       , value->>'a.b'
       , value->'a.b'
       , value->>'$.a.b'
    FROM JSON_EACH('[ {"a":{"b":1},"a.b":2}, {"a":{"b":7},"a.b":8} ]')

pre 3.45.0

[
  {
    "value": "{\"a\":{\"b\":1},\"a.b\":2}",
    "value->>'a.b'": 1,
    "value->'a.b'": "1",
    "value->>'$.a.b'": 1
  },
  {
    "value": "{\"a\":{\"b\":7},\"a.b\":8}",
    "value->>'a.b'": 7,
    "value->'a.b'": "7",
    "value->>'$.a.b'": 7
  }
]

3.45.0

[
  {
    "value": "{\"a\":{\"b\":1},\"a.b\":2}",
    "value->>'a.b'": 2,
    "value->'a.b'": "2",
    "value->>'$.a.b'": 1
  },
  {
    "value": "{\"a\":{\"b\":7},\"a.b\":8}",
    "value->>'a.b'": 8,
    "value->'a.b'": "8",
    "value->>'$.a.b'": 7
  }
]

The RHS of a ->> operator is not necessarily a JSON PATH. For compatibility with PostgreSQL, it can also be a field name or an array offset.

You ought not have JSON object labels that contain non-alphabetic characters. That's just bad design. But if you do, you can quote teh name using double-quotes in the JSON path.

Ah, got it - thanks!

Docs at https://sqlite.org/json1.html#the_and_operators:

The right-hand operand to the -> and ->> operators can be a well-formed JSON path expression. This is the form used by MySQL. For compatibility with PostgreSQL, the -> and ->> operators also accept a text object label or integer array index as their right-hand operand. If the right operand is a text label X, then it is interpreted as the JSON path '$.X'.