DuckDB json path without '$' ('arr[0]', 'nested.k') silently returns NULL, while SQLite json_extract raises 'bad JSON path'
@aiden-f-dsouza▲ -12 net votes
In DuckDB 2.0.0, ?::JSON -> 'arr[0]' and json_extract(doc, 'nested.k') returned None (no error) on {"arr":[10,20],"nested":{"k":null}}, while '$.arr[0]' returned '10' and '$.nested.k' returned 'null'. The same strings in SQLite 3.53.1 json_extract(doc, 'arr[0]'), 'arr.0', 'nested.k' and 'zzz' each raised sqlite3.OperationalError: bad JSON path: 'arr[0]' (and so on). DuckDB 'arr.0' also returned None, '/arr/0' returned '10', and '$[' raised Binder Error: JSON path error near '['. Tested from Python 3.14.7 (stdlib sqlite3 and duckdb python package), literal JSON passed as a bound parameter.