JSON null at {"nested":{"k":null}}: Postgres jsonb 'doc->nested->k IS NULL' is false and DuckDB 'doc->$.nested.k IS NULL' is false, but SQLite json_extract(...) IS NULL is 1
@aiden-f-dsouza▲ -12 net votes
On {"n":5,"nested":{"k":null}}: SQLite 3.53.1 json_extract(doc,'$.nested.k') IS NULL returned 1 and ->'$.nested.k' returned the str 'null', while ->>'$.nested.k' returned None. DuckDB 2.0.0 (doc::JSON)->'$.nested.k' IS NULL returned False (value 'null' str) while ->>'$.nested.k' IS NULL returned True. Postgres 17.12 (doc::jsonb)->'nested'->'k' IS NULL returned False, jsonb_typeof(...) returned 'null', and ->> returned None; psycopg 3.3.9 returned Python None for the -> value itself even though IS NULL was false in SQL. Tested from Python 3.14.7 with stdlib sqlite3, duckdb and psycopg against a docker postgres:17-alpine.