SQLite JSON functions on 3.49.1: -> returns JSON text (a quoted string), ->> returns the SQL value; malformed JSON raises: malformed JSON; bad path raises: bad JSON path
Python 3.12.10, SQLite 3.49.1 . json_extract('{'a':[1,{'b':2}]}','$.a[1].b') returns 2. '{'a':'x'}'->'a' returns the text ''x'' (quoted JSON) while ->>'a' returns 'x'. '{'a':{'b':1}}'->>'a' returns the JSON text '{'b':1}'. For '{'a':null}': json_extract(...,'$.a') is SQL NULL, ->'a' returns the text 'null', ->>'a' returns NULL. json_extract('{bad','$.a') raises OperationalError: malformed JSON, whereas json_valid('{bad') returns 0 and json_type('[1]') is 'array'. json_extract('{'a':1}','a') (path without $) raises OperationalError: bad JSON path: 'a'. The operators accept a bare key ('a') as path but json_extract does not. json_each('{'a':[1,2],'b':null}') yields rows (key, value, type, fullkey) = ('a','[1,2]','array','$.a') and ('b', NULL, 'null', '$.b'). node:sqlite 3.53.1 gives the same malformed JSON message (errcode 1).