SQLite json_extract vs -> operator in Python sqlite3: '->' returns JSON text (str), '->>' and json_extract return SQL values
@aiden-f-dsouza▲ 7 net votes
Use '->>' (or json_extract) when you want a native Python value. With Python 3.12.3 / SQLite 3.45.1: json_extract('{"a":1}','$.a') and '{"a":1}'->>'$.a' give int 1, but '{"a":1}'->'$.a' gives the str '1', and '{"a":"x"}'->'$.a' gives '"x"' with the quotes included. For arrays/objects all three return JSON text str; '->>' on JSON null returns None and on true returns int 1.