← All notes

Summary

SQLite supports JSON through functions and operators rather than a separate SQL JSON storage class. JSON text fits into SQLite's existing value model. [S1, S2]

Details

With one path, json_extract returns SQL scalars for scalar JSON values, including SQL NULL for JSON null. Arrays and objects are returned as JSON text. Multiple paths produce a JSON array in text form. json_valid checks a value against its selected rules; when its flags argument is omitted, it checks strict RFC-8259 text. SQLite JSONB is a separate binary representation described by the JSON documentation, not a new storage class. [S1, S2]

Claims

• id: C1 claim: "SQLite cannot add a separate JSON type to its existing value classes." source: S1 evidence: "It is not possible to add a new \"JSON\" type"
• id: C2 claim: "A single-path JSON null extraction returns SQL NULL." source: S1 evidence: "NULL for a JSON null"
• id: C3 claim: "SQLite associates a value type with the value rather than its container." source: S2 evidence: "associated with the value itself, not with its container"

Sources

• [S1] JSON functions and operators. SQLite authors. https://www.sqlite.org/json1.html. Public domain; license: https://www.sqlite.org/copyright.html. Accessed 2026-11-05.
• [S2] Datatypes in SQLite. SQLite authors. https://www.sqlite.org/datatype3.html. Public domain; license: https://www.sqlite.org/copyright.html. Accessed 2026-11-05.

Attribution and notices

This note is CC BY 4.0. Source material has been condensed and restated; short evidence excerpts retain their source license.

SQLite code and documentation are dedicated to the public domain by their authors. This note is original synthesis.

@misc{iil_note_cn0qb6bf,
  author = {{Librarian}},
  title = {{SQLite JSON functions return SQL values as well as JSON text}},
  howpublished = {iinventedlight Library},
  url = {https://iinventedlight.com/library/notes/sqlite-json/},
  note = {Permanent ID iil:note:cn0qb6bf. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/sqlite-json.md.