SQLite makes JSON a default part of the engine

An application that uses SQLite's JSON functions has depended on a build decision outside its SQL. The functions existed, but their presence required enabling the JSON extension when compiling the library. SQLite 3.38.0, released on February 22, changes that default. JSON functions are built in, and disabling them now requires SQLITE_OMIT_JSON. The earlier SQLITE_ENABLE_JSON1 option is no longer necessary. Applications still need to account for deliberately reduced builds, but the standard configuration includes JSON support.

The same release adds -> and ->> extraction operators. These are useful together with the changed default because portable SQL depends on both availability and meaning. A query can now express document access more briefly, but the choice between the operators determines the type passed to the rest of the expression. For an embedded database, making those rules accessible without extension setup is a meaningful improvement in application packaging.

I like SQLite's small operational footprint, and built-in JSON fits that preference. A program can retain relational storage while inspecting structured values in SQL. The interesting question is how clearly the database distinguishes a JSON representation from the SQL value represented by it. This release gives that distinction explicit syntax.

The implementation remains text-based

The 3.38.0 JSON implementation explains its history directly. It began in 2015 as ext/misc/json1.c and has moved into the core. Its introductory comments say that JSON is stored as text and that this implementation has no JSONB support. Becoming a built-in function family does not introduce a binary document storage format.

That fact separates two concerns that are easy to conflate. Compiling JSON support into the library determines which expressions a program can execute. Encoding a document determines what bytes represent its contents. The release changes the former. A design that needs predictable relational columns still has to choose those columns, and storing a document as text still leaves document interpretation to the JSON functions.

Inside the implementation, parsed nodes have distinct categories for null, booleans, integers, real numbers, strings, arrays, and objects. A separate JSON subtype marks values that the implementation treats as JSON results. These details explain why two expressions can produce similar visible text while behave differently when another JSON function consumes them. Representation includes information about the value's role in the expression, beyond the characters displayed by a command-line client.

The registration code maps both new operators to the extraction implementation. It supplies different flags for JSON output and SQL output. This makes their relationship precise. They share the process of locating a document member, then choose how to return what they found. The difference is part of evaluation, so choosing an operator is a decision about the next expression's input.

Extraction has two useful return contracts

The release's JSON regression tests exercise the operators on a document member named a. The cases include a JSON null, an integer, a real number, a string, an array, an object, and an absent member. They compare the results with json_extract and inspect both ordinary SQL types and the JSON subtype.

For a string, -> returns its JSON representation, including the quotation marks required by JSON. The ->> operator returns the SQL text value. For a number, the first operator returns JSON text and the second returns a SQL numeric value. A program displaying either result might show the same digits, while subsequent SQL sees different types.

SELECT '{"a":"six"}' -> 'a';
-- JSON text containing "six", including the double quotes

SELECT '{"a":"six"}' ->> 'a';
-- SQL text containing six

SELECT typeof('{"a":123}' -> 'a');
-- text

SELECT typeof('{"a":123}' ->> 'a');
-- integer

Those examples use cases present in the release tests. A comparison, aggregate, or application binding receives the SQL type of the expression, so the type distinction is relevant even when the display looks unsurprising. Selecting the SQL-returning operator is appropriate when the document member becomes a scalar in a relational expression. Selecting the JSON-returning operator preserves a JSON representation for further document work.

Arrays and objects need particular care. The test expects ->> to return their serialized text with an ordinary text type. The corresponding -> result carries the JSON subtype. SQL has no array or object value in this implementation into which those structures can be converted directly. The SQL-returning operator therefore cannot turn every possible JSON result into a non-text scalar.

The tests also compare these operators with json_extract. For a single path selecting an integer, that function returns an integer. For a selected array, it returns a JSON result. Calling the new operators a simple spelling change for that function would miss the fixed return contract that each operator expresses.

Missing members and null need separate reasoning

The difference between an absent member and a present JSON null becomes visible with ->. In the tests, extracting the null member returns JSON text null, while extracting a missing member returns SQL NULL. With ->>, both cases produce SQL NULL. That conversion is useful when SQL null propagation is desired, but it loses information about why the result is null.

SELECT '{"a":null}' -> 'a';
-- JSON text null

SELECT '{"a":null}' ->> 'a';
-- SQL NULL

SELECT '{"b":999}' -> 'a';
-- SQL NULL because a is absent

A document-processing application may need to distinguish a user who explicitly cleared a field from a document that never supplied it. The operator contract determines whether that distinction remains available in the extracted result. This is a data-model question before it is a syntax preference. A query that deliberately maps both states to SQL NULL should make that choice consistently with the application consuming its rows.

The test suite also demonstrates integer operands for array access. Given an array, x->0 extracts the first element as JSON, and x->>0 extracts it under the SQL-returning rules. The release announcement describes compatibility with MySQL and PostgreSQL. The concrete tests are more useful than inferring complete cross-database equivalence from that statement. They establish behavior for member names and array offsets in this SQLite version.

Constructing JSON requires the same distinction

The older construction tests remain relevant after JSON becomes built in. One test calls json_object with a SQL string containing [52,3.14159]. Its result contains a JSON string with those characters. Another wraps the same input in json, and the result contains an actual JSON array. Both outputs are valid JSON, but their structures differ.

SELECT json_object('ex', '[52,3.14159]');
-- {"ex":"[52,3.14159]"}

SELECT json_object('ex', json('[52,3.14159]'));
-- {"ex":[52,3.14159]}

This is a useful example because neither query needs to fail to expose an application mistake. An API expecting an array can receive a string that happens to resemble one. The distinction is easy to miss when reading a document as printed text. Explicitly identifying which expressions produce JSON makes it possible to reason about construction without relying on appearance.

The same suite checks that json_array(1,2,'3',4) preserves the third argument as a string. It also exercises insertion, replacement, and setting by document path. Those functions already form a document manipulation system. The default inclusion broadens access to that system, while the new operators make extraction contracts more visible in ordinary SQL.

Validation and traversal remain separate operations

The construction and extraction functions sit alongside validation and table-valued traversal. The release tests reject leading zeros in JSON numbers and reject unescaped control characters inside strings. A text value that resembles a document therefore need not be accepted by the parser. Making the functions available by default does not relax their input grammar.

The tests use json_each to expand phone-number arrays into rows and json_tree to search nested structures. This gives SQL access to individual values without requiring the application to fetch and decode every document first. The example containing mixed plain-text phone numbers and JSON arrays uses json_valid before traversing the array branch. Its need arises from the stored values, independently of how the functions were compiled into the library.

These examples clarify the design responsibility left to the application. A column holding arbitrary text requires different query handling from one whose contents are always JSON. Default availability removes the extension prerequisite, while validation and traversal still depend on the conventions chosen for the data.

Built-in availability does not choose a schema

The release contains other changes, including Bloom-filter optimization for large analytic queries and new virtual-table interfaces. Those changes concern query execution and extension integration. They do not imply that placing every attribute inside JSON is a database design improvement. The JSON implementation and its tests describe available operations, leaving the application responsible for deciding which values belong in documents.

I prefer the new default because it reduces a packaging condition that can otherwise make identical SQL behave differently across builds. The return-type rules deserve equal attention. They let a query state whether it needs a JSON representation or an SQL scalar, and the tests show where that conversion preserves information and where it collapses distinct document states. An application can use those rules to define its document boundaries explicitly while keeping the rest of its data relational.