SQLite Built Valid JSON. Why Is the Nested Object a String?

A SQLite query can succeed, return syntactically valid JSON, and still violate the response contract you meant to implement.

The failure is easy to miss because the offending value looks so plausible. Suppose a producer starts with the intact text {"device":"sensor-A","retries":2} and builds an envelope with json_object('payload', ?). SQLite can legally return:

{"payload":"{\"device\":\"sensor-A\",\"retries\":2}"}

That document parses. Its quotes and escapes are correct. Nothing has been truncated. Yet a consumer that expects payload.device cannot treat payload as an object, because payload is a JSON string. Under JSON itself, strings and objects are distinct value types; syntactic validity does not make them application-equivalent. RFC 8259 defines both as separate JSON values.

The operational question is therefore not merely, “Did SQLite build valid JSON?” It is: Does the constructed envelope contain the required JSON object, or a valid JSON string whose characters merely resemble that object?

This playbook answers that question with one immutable source object, eight construction paths, six shape-admission controls, SQL-level type inspection, Python decoding, and an independently authored expected document. The supplied commissioning record is bounded to CPython 3.12.14 and SQLite 3.53.1 on an owned in-memory connection. It demonstrates construction semantics; it does not measure production prevalence, network behavior, ORM mutation tracking, JSONB, JSON5 qualification, optimizer subtype propagation, or production repair.

Define the object that the response contract actually requires

Start by writing the response contract in semantic terms rather than by staring at the output string.

For this laboratory, the required document is:

{"payload":{"device":"sensor-A","retries":2}}

payload must be an object. Within that object, device must equal "sensor-A" and retries must equal the small integer 2. The corresponding Python expectation is:

{"payload": {"device": "sensor-A", "retries": 2}}

The rejected alternative is also valid JSON:

{"payload":"{\"device\":\"sensor-A\",\"retries\":2}"}

Its Python representation after one ordinary json.loads() call is:

{"payload": '{"device":"sensor-A","retries":2}'}

Those documents do not express the same application value. A consumer expecting an object can evaluate something equivalent to payload["device"]; a consumer receiving the second document instead gets a string at payload. RFC 8259 explicitly distinguishes structured objects from primitive strings.

The article’s decision therefore follows the declared interface, not whether the text happens to resemble JSON. An application could legitimately define payload as a string carrying JSON source text. This contract does not.

The integer 2 is intentionally small and exact. Numeric identity is a different integrity boundary; see Refonte Learning’s discussion of JSON identifier preservation at a separate boundary. That article deals with numeric representation and large identifiers, whereas this experiment holds the source value constant and asks whether the containing member is an object or quoted text.

Before executing any SQL, fix two independent expectations: type(decoded["payload"]) is dict, and complete equality with the expected document. The first proves the required nested shape. The second prevents an object with the wrong members or values from being approved merely because its root type is correct.

Locate the value argument where JSON becomes quoted text

The important SQLite boundary is the value argument accepted by constructors such as json_object().

SQLite’s JSON documentation says that ordinary value arguments are normally treated as literal values. Text that looks exactly like valid JSON is still quoted when it is supplied as an ordinary SQL TEXT value. By contrast, a value argument that comes directly from another JSON function or from the -> operator is treated as JSON structure; the ->> operator does not receive that treatment. SQLite’s JSON Functions and Operators, sections 3.4 and 4.13, document this distinction explicitly.

Keep three ideas separate:

1. SQLite storage class: the source can be SQL TEXT.

2. Textual representation: those characters can spell a complete JSON object.

3. JSON value type in the constructed document: the result can still be either a JSON string or a JSON object depending on the expression feeding the constructor.

SQLite has ordinary storage classes such as NULL, INTEGER, REAL, TEXT, and BLOB; it does not turn a normal TEXT column into a persistent structured-object datatype merely because its contents are JSON text. SQLite’s datatype documentation describes TEXT as a storage class, while the JSON documentation explains that JSON text is stored using SQLite’s existing storage model.

That distinction explains why this is a construction problem rather than evidence of source corruption. json(payload) at the relevant expression boundary tells SQLite to interpret valid textual JSON as JSON for that direct JSON-producing expression. It does not mean every client-side string that was once produced by json() will retain some persistent structured marker after it has been fetched into Python and bound again.

Check syntax and nested type separately

A syntax check answers a narrower question than a shape check.

For these envelopes:

{"payload":"{\"device\":\"sensor-A\",\"retries\":2}"}

and:

{"payload":{"device":"sensor-A","retries":2}}

the fixture expects:

SELECT json_valid(?);

to return 1 for both. SQLite documents that one-argument json_valid() tests whether text is well-formed RFC 8259 JSON; in current SQLite it defaults to the strict text interpretation represented by flag 1. It does not ask whether $.payload is an object required by your application.

The second observation is therefore:

SELECT json_type(?, '$.payload');

For a JSON string, SQLite’s JSON type label is text. For a JSON object, it is object; json_type() can also report values such as array, integer, real, null, true, and false.

Do not confuse SQLite’s JSON type label text with Python’s type name str. They describe values at different layers. The fixture deliberately records both layers by using json_type() inside SQLite and json.loads() in Python.

Write the expected document before building the query

The fixture uses four immutable values:

RAW = '{"device":"sensor-A","retries":2}'
ENVELOPE = '{"payload":{"device":"sensor-A","retries":2}}'
EXPECTED = {"payload": {"device": "sensor-A", "retries": 2}}
TEXT_EXPECTED = {"payload": RAW}

RAW is the source text. ENVELOPE exists so the arrow-operator cases can select an object-valued $.payload path. EXPECTED is the accepted application document. TEXT_EXPECTED records the deliberately wrong shape produced by the four text-producing construction paths.

The critical design decision is that EXPECTED is not generated with the SQL expression under test. It is authored from the response contract. Otherwise, a test can become circular: construct a document with the candidate implementation, derive the expectation from that result, then congratulate the candidate for matching itself.

For this fixed corpus, complete equality matters:

decoded == EXPECTED

A weaker check such as:

type(decoded.get("payload")) is dict

would prove only that payload is an object. It would not catch, for example:

{"payload": {"device": "sensor-B", "retries": 2}}

or:

{"payload": {"device": "sensor-A", "retries": 3}}

Both have the correct nested type and the wrong document.

The SQL itself remains deliberately small: placeholders, json_object(), json(), json_valid(), json_type(), and two extraction operators. Readers who need broader query-review practices can use Refonte Learning’s SQL query-validation foundations; that material emphasizes defining correctness before optimization, but this laboratory does not expand into general SQL instruction.

An independent oracle is especially valuable when output is syntactically respectable. If malformed JSON were the only possible failure, a parser would do most of the work. Here, the dangerous result parses successfully and differs only in application-level shape.

Run the complete fourteen-case laboratory

The supplied fixture uses CPython’s Python 3.12 sqlite3.connect(..., autocommit=True) API. Python documents the autocommit connection parameter and Connection.autocommit beginning in Python 3.12; with True, the connection uses SQLite autocommit mode. Python’s documentation also states that sqlite3.sqlite_version reports the runtime SQLite library version, so it must be checked separately from the Python interpreter version.

The commissioning record supplied for this article reports CPython 3.12.14 and SQLite 3.53.1. The test itself actively requires SQLite 3.38.0 or later and verifies that json_valid() works before proceeding. SQLite 3.38.0 was released on 2022-02-22; that release introduced -> and ->> and made the JSON functions built in by default unless SQLite is compiled to omit them. These operators are established 2022 functionality, not a 2026 launch. SQLite 3.38.0 release notes.

The database is :memory: and owned by this process. Python documents ":memory:" as creating an in-memory SQLite database. The fixture uses an ordinary TEXT NOT NULL column, ordinary placeholders, default type adaptation for these values, and an explicitly closed connection. Python’s default mapping sends str to SQLite TEXT, sends None to SQL NULL, and returns TEXT as Python str when the default text_factory is in use.

No network, API service, existing database, ORM, or external input is involved.

Preserve the executable source and its run identity

Save the following exact source as db_json_shape_fixture.py:

"""Owned in-memory SQLite JSON construction fixture; no external input or writes."""
import hashlib
import json
import platform
import sqlite3
from datetime import datetime, timezone
from pathlib import Path

REVISION = "sqlite-json-object-shape-v1"
RAW = '{"device":"sensor-A","retries":2}'
ENVELOPE = '{"payload":{"device":"sensor-A","retries":2}}'
EXPECTED = {"payload": {"device": "sensor-A", "retries": 2}}
TEXT_EXPECTED = {"payload": RAW}
rows = []


def require(condition, message):
    if not condition:
        raise AssertionError(message)


def construct(db, case_id, sql, params, expected, expected_type):
    output = db.execute(sql, params).fetchone()[0]
    valid, kind = db.execute(
        "SELECT json_valid(?), json_type(?, '$.payload')", (output, output)
    ).fetchone()
    decoded = json.loads(output)
    accepted = type(decoded.get("payload")) is dict and decoded == EXPECTED
    require(valid == 1, case_id + ": malformed envelope")
    require(kind == expected_type, case_id + ": unexpected nested type")
    require(decoded == expected, case_id + ": independent document mismatch")
    require(accepted == (expected_type == "object"), case_id + ": policy mismatch")
    rows.append({"id": case_id, "sql": sql, "params": params,
                 "output": output, "json_valid": valid, "payload_type": kind,
                 "decoded": decoded, "decision": "ACCEPT_OBJECT" if accepted
                 else "REJECT_STRING_PAYLOAD", "status": "PASS"})


def admit_object(db, raw):
    if type(raw) is not str:
        return "REJECT_INPUT_TYPE", None
    if db.execute("SELECT json_valid(?)", (raw,)).fetchone()[0] != 1:
        return "REJECT_INVALID_JSON", None
    # Separate statement: json_type never receives a malformed candidate here.
    if db.execute("SELECT json_type(?)", (raw,)).fetchone()[0] != "object":
        return "REJECT_ROOT_TYPE", None
    output = db.execute("SELECT json_object('payload', json(?))", (raw,)).fetchone()[0]
    return "ADMIT_OBJECT_SHAPE", json.loads(output)


def main():
    started = datetime.now(timezone.utc).isoformat()
    db = sqlite3.connect(":memory:", autocommit=True)
    try:
        require(sqlite3.sqlite_version_info >= (3, 38, 0), "SQLite 3.38+ required")
        require(db.execute("SELECT json_valid(?)", (RAW,)).fetchone()[0] == 1,
                "JSON capability missing")
        db.execute("CREATE TABLE source (id INTEGER PRIMARY KEY, payload TEXT NOT NULL)")
        db.execute("INSERT INTO source VALUES (1, ?)", (RAW,))
        stored = db.execute("SELECT payload, typeof(payload) FROM source WHERE id=1").fetchone()
        require(stored == (RAW, "text"), "Source text was changed")
        construct(db, "J01_bound_text", "SELECT json_object('payload', ?)",
                  (RAW,), TEXT_EXPECTED, "text")
        construct(db, "J02_direct_json", "SELECT json_object('payload', json(?))",
                  (RAW,), EXPECTED, "object")
        construct(db, "J03_stored_text", "SELECT json_object('payload', payload) FROM source WHERE id=1",
                  (), TEXT_EXPECTED, "text")
        construct(db, "J04_stored_reparsed", "SELECT json_object('payload', json(payload)) FROM source WHERE id=1",
                  (), EXPECTED, "object")
        construct(db, "J05_arrow_json", "SELECT json_object('payload', ? -> '$.payload')",
                  (ENVELOPE,), EXPECTED, "object")
        construct(db, "J06_arrow_sql_text", "SELECT json_object('payload', ? ->> '$.payload')",
                  (ENVELOPE,), TEXT_EXPECTED, "text")
        returned = db.execute("SELECT json(?)", (RAW,)).fetchone()[0]
        require(type(returned) is str and returned == RAW, "Unexpected Python return")
        construct(db, "J07_rebound_result", "SELECT json_object('payload', ?)",
                  (returned,), TEXT_EXPECTED, "text")
        construct(db, "J08_rebound_reparsed", "SELECT json_object('payload', json(?))",
                  (returned,), EXPECTED, "object")
        gate_cases = [
            ("J09_gate_object", RAW, "ADMIT_OBJECT_SHAPE", EXPECTED),
            ("J10_gate_array", '["sensor-A",2]', "REJECT_ROOT_TYPE", None),
            ("J11_gate_json_string", '"sensor-A"', "REJECT_ROOT_TYPE", None),
            ("J12_gate_json_null", "null", "REJECT_ROOT_TYPE", None),
            ("J13_gate_invalid", '{"device":', "REJECT_INVALID_JSON", None),
            ("J14_gate_sql_null", None, "REJECT_INPUT_TYPE", None),
        ]
        for case_id, raw, expected_decision, expected_output in gate_cases:
            decision, output = admit_object(db, raw)
            require((decision, output) == (expected_decision, expected_output),
                    case_id + ": independent gate expectation mismatch")
            rows.append({"id": case_id, "raw": raw, "decision": decision,
                         "output": output, "status": "PASS"})
        require(db.execute("SELECT payload, typeof(payload) FROM source WHERE id=1").fetchone()
                == stored, "Construction altered stored source")
        require(len(rows) == 14, "Incomplete matrix")
    finally:
        db.close()
    print(json.dumps({"status": "PASS_14_CASES", "revision": REVISION,
                      "source_sha256": hashlib.sha256(Path(__file__).read_bytes()).hexdigest(),
                      "python": platform.python_version(), "sqlite": sqlite3.sqlite_version,
                      "platform": platform.platform(), "started_utc": started,
                      "finished_utc": datetime.now(timezone.utc).isoformat(),
                      "rows": rows}, indent=2))


if name == "__main__":
    main()

Run it with:

python db_json_shape_fixture.py > db_json_shape_results.json

The supplied commissioning record reports the final status:

PASS_14_CASES

and the source SHA-256:

b3df9a7165bc847ae5a1c5b5756cab7677f35ab63d5c892edbca873bb4a234c8

That digest identifies the fixture file bytes read by that Python process. It is not an execution signature and does not independently prove that a particular machine ran the code. The commissioning evidence also records revision sqlite-json-object-shape-v1, runtime versions, platform, UTC start and finish timestamps, and every case row.

PASS_14_CASES must be interpreted correctly. It means all fourteen diagnostic expectations agreed with the supplied oracle. Some expected outcomes are rejections. A passing J13 proves that malformed input was rejected at the designated validation stage; it does not make the malformed input acceptable. Likewise, passing J01 proves that the fixture observed the expected quoted-string failure.

Treat a missing final JSON report, a traceback, fewer than fourteen rows, a changed source digest, unexpected runtime preconditions, or a failed require() as incomplete or contradictory evidence. Do not discard the failing row and recalculate success from whatever remains.

Compare bound text with a direct JSON-producing expression

J01 and J02 use exactly the same RAW characters and keep the value parameterized. The only intentional change is the expression that feeds the json_object() value argument.

J01:

SELECT json_object('payload', ?)

with:

{"device":"sensor-A","retries":2}

produces the expected text-shaped envelope:

{"payload":"{\"device\":\"sensor-A\",\"retries\":2}"}

After Python decodes the complete envelope once:

{"payload": '{"device":"sensor-A","retries":2}'}

J02 instead uses:

SELECT json_object('payload', json(?))

and produces:

{"payload":{"device":"sensor-A","retries":2}}

which decodes as:

{"payload": {"device": "sensor-A", "retries": 2}}

SQLite documents precisely this distinction: ordinary TEXT supplied to a JSON constructor value argument is normally quoted, even if it contains well-formed JSON, while the direct result of a JSON function such as json() is inserted as JSON structure.

Parameter binding is not the problem, and string interpolation is not the repair. Python’s sqlite3 documentation recommends placeholders for binding values; the fixture retains placeholders in both cases.

What json(?) contributes here is a construction-time interpretation: the bound text is validated/interpreted as JSON and its JSON structure is supplied directly to json_object(). It is not an object-only validator. JSON itself permits arrays, strings, numbers, booleans, and null as values, and SQLite’s JSON functions likewise accept nonobject JSON roots where applicable.

That is why J02 solves this fixed construction case but does not eliminate the later admission gate.

Inspect the result with both SQL and Python

The two outputs have the same syntax result:

This is the core diagnostic pattern. A successful json_valid() permits you to say that the envelope is syntactically valid under the test’s JSON rules. It does not permit you to say that the nested type satisfies the response contract. SQLite documents those checks separately: json_valid() reports well-formedness, while json_type() reports the selected JSON value type.

Python then provides an independent reader of the complete document. The fixture does one json.loads(output) and compares the resulting Python object with a separately authored expectation.

Do not turn the J01 symptom into a general “decode strings until they become objects” algorithm. That would erase the distinction between intentionally string-valued data and a constructor error. The repair belongs where the producer knows the intended contract.

Prove that storage did not corrupt the source

J03 and J04 move the same comparison from a bound Python value to an ordinary database column.

The fixture creates:

CREATE TABLE source (
    id INTEGER PRIMARY KEY,
    payload TEXT NOT NULL
)

and inserts RAW through a placeholder. It then records:

stored == (RAW, "text")

That proves two bounded facts about this in-memory run: the characters retrieved from the source column equal the original RAW, and SQLite reports the stored value’s storage class as text.

J03 passes that column directly:

SELECT json_object('payload', payload)
FROM source
WHERE id=1

Expected result:

{"payload":"{\"device\":\"sensor-A\",\"retries\":2}"}

J04 reparses at construction:

SELECT json_object('payload', json(payload))
FROM source
WHERE id=1

Expected result:

{"payload":{"device":"sensor-A","retries":2}}

The source row does not need an update. The fixture repeats the original (payload, typeof(payload)) query after all fourteen cases and requires it to equal the earlier stored tuple. SQLite’s documented constructor behavior explains why an ordinary SQL TEXT value is quoted and why json(payload) changes its interpretation at the direct construction boundary without requiring mutation of the stored source.

This is separate from an ORM failing to persist a nested edit. Refonte Learning’s article on JSON mutation and database readback addresses mutation detection and fresh-session persistence. Here the source is already present and unchanged; the experiment tests only how a query constructs its output.

Do not generalize the fixture’s unchanged-source result to an unknown database. It proves a precondition inside this owned test. A production investigation still has to establish its own source provenance and storage state.

Track the arrow operators and the client roundtrip

J05 and J06 start from the same complete envelope:

ENVELOPE = '{"payload":{"device":"sensor-A","retries":2}}'

and select the same object-valued path.

J05:

SELECT json_object('payload', ? -> '$.payload')

produces an object-valued payload.

J06:

SELECT json_object('payload', ? ->> '$.payload')

produces a quoted string containing the object’s textual representation.

SQLite documents the distinction: -> returns a JSON representation of the selected subcomponent, while ->> returns its SQL representation. For a json_object() value argument, direct -> output retains JSON treatment, while ->> does not.

For this object case, that yields:

J05 -> payload_type = object -> ACCEPT_OBJECT
J06 -> payload_type = text   -> REJECT_STRING_PAYLOAD

The laboratory deliberately does not extrapolate through every scalar and null combination. SQLite documents different SQL-level representations for strings, numbers, and nulls, but those combinations are outside this corpus.

J07 and J08 test a different boundary. First:

returned = db.execute("SELECT json(?)", (RAW,)).fetchone()[0]

The fixture requires:

type(returned) is str
returned == RAW

That is consistent with Python’s default SQLite mapping: SQLite TEXT results are returned as Python str under the default text_factory.

A returned string needs a new construction decision

Once returned is an ordinary Python str, J07 binds it like any other string:

SELECT json_object('payload', ?)

Expected result: JSON string at payload.

J08 makes the interpretation explicit again:

SELECT json_object('payload', json(?))

Expected result: JSON object at payload.

Nothing in the experiment says the characters were corrupted. They are required to remain equal to RAW. Nothing requires a theory about hidden Python metadata being stripped. The bounded observation is simpler: a JSON-producing SQL expression returned text through sqlite3; Python received an ordinary str; rebinding that str supplied ordinary TEXT to the next SQL statement. Python documents both sides of that mapping: str binds as TEXT, and TEXT returns as str by default.

This client roundtrip is intentionally different from untested optimizer questions. The experiment does not qualify subtype propagation through CTEs, subqueries, aggregation, ordering, custom adapters, or other SQL rewrites. Those require separate evidence.

Read the construction ledger as eight separate claims

The eight construction paths form the primary evidence matrix.

All eight documents are expected to satisfy the syntax check. Four satisfy the independent object contract, and four contain quoted text instead.

The direct SQLite documentation predicts the construction split: ordinary TEXT values are normally quoted; direct JSON-function results and direct -> results are treated as JSON; ->> is excluded from that rule. The client-roundtrip pair adds the Python behavior explicitly exercised by the fixture.

These are eight separate claims, not merely a score of four versus four. Aggregate counts can conceal swapped outcomes. A broken implementation in which J02 became text and J01 became an object could still produce “four accepted, four rejected” if some other cases changed in the opposite direction.

A reviewer should therefore reconcile every case identifier with its exact SQL expression, parameter source, output string, nested type, decoded value, and expected decision. Case identity is part of the evidence.

The ledger uses REJECT_STRING_PAYLOAD because that is the fixture’s diagnostic label. At the operational level, when the declared contract requires an object and the producer has built a string, the repair decision is REPAIR_CONSTRUCTION: change the constructor boundary rather than relabeling the quoted result as acceptable.

Admit object-shaped inputs without confusing the rejection stages

json(?) is sufficient to produce an object for RAW, but json() alone is not an object-schema gate. An array or JSON string can also be valid JSON.

The admit_object() helper therefore applies three sequential preconditions:

if type(raw) is not str:
    return "REJECT_INPUT_TYPE", None

First, the caller must provide exactly the Python input type expected by this helper.

Next:

if db.execute(
    "SELECT json_valid(?)", (raw,)
).fetchone()[0] != 1:
    return "REJECT_INVALID_JSON", None

Only valid JSON text proceeds.

Then:

if db.execute(
    "SELECT json_type(?)", (raw,)
).fetchone()[0] != "object":
    return "REJECT_ROOT_TYPE", None

Only an object root proceeds to construction.

Finally:

output = db.execute(
    "SELECT json_object('payload', json(?))", (raw,)
).fetchone()[0]

SQLite documents why those checks answer different questions. json_valid() establishes syntactic validity under the selected rules, while json_type() reports the root or path type and will raise on malformed JSON.

The helper deliberately uses separate statements. A malformed candidate is rejected by json_valid() before it is ever passed to json_type(). That avoids making the gate depend on assumptions about SQL boolean-expression evaluation order or error suppression.

Reject each wrong root at its own boundary

The six admission controls have independent expected outcomes:

The naming of J14 deserves care. Python documents None as binding to SQL NULL, but in this helper it never reaches SQLite because the Python type gate rejects it first. Therefore J14 is not evidence about json_type(NULL). It is evidence that the helper’s first boundary rejects a non-str caller value.

J10 through J12 show why “valid JSON” is not enough for an object-only contract. Arrays, strings, and null are valid JSON value kinds, but their root type does not meet this response requirement. RFC 8259 distinguishes all of those value categories.

This helper still does not validate required object member names, member types, additional-field policy, numeric ranges, authorization, size limits, depth policy, or duplicate names. Duplicate-member handling is a separate parser-boundary concern; Refonte Learning’s duplicate-name validation before decoding covers that distinct problem.

Set acceptance criteria for the actual consumer handoff

The construction rows and the generic admission helper deliberately enforce different acceptance strengths.

For J01 through J08, the fixture has a fixed corpus and therefore uses exact-document acceptance:

accepted = (
    type(decoded.get("payload")) is dict
    and decoded == EXPECTED
)

For that corpus, ACCEPT_OBJECT means both the required nested type and the complete expected content matched.

admit_object(), by contrast, proves only a narrower property before construction: the candidate is exactly a Python str, it is syntactically valid JSON under the fixture’s one-argument json_valid() test, and its JSON root type is object. Its return label correctly says ADMIT_OBJECT_SHAPE, not “schema valid” or “authorized.”

A real handoff contract still needs definitions for required keys, field types, optional fields, additional-field policy, semantic ranges, encoding expectations, schema versioning, and which component owns rejection. None of those are established merely by observing json_type(...)=object.

The next integration test should therefore be broader than this laboratory. A proposed test (not executed here) is to pass the emitted envelope through the actual consumer implementation and prove that the consumer resolves the same logical record: for example, it sees payload.device == "sensor-A" and payload.retries == 2 through its normal access path, not through a compensating second JSON decode. That check belongs at the integration boundary because it validates the meaning the consumer actually receives.

Local release acceptance should require complete evidence: all expected rows present, runtime preconditions recorded, the intended construction route identified, syntax valid, nested type correct, and independently decoded content equal to its specified expectation. A missing diagnostic or an unexpected exception is not a reason to weaken the oracle; it is incomplete qualification.

Reconcile source, query and output before changing a pipeline

A useful investigation ledger should preserve enough information to reconstruct where the type changed. At minimum, record source provenance, the exact value bound or retrieved, the constructor expression, Python and SQLite versions, output text, json_valid, nested json_type, the decoded Python value, and the final case decision.

For this fixture, the reconciliation chain is unusually clean:

RAW
  -> stored as SQL TEXT unchanged
  -> supplied through one of eight constructor routes
  -> complete output text recorded
  -> syntax checked
  -> $.payload type checked
  -> full document decoded independently
  -> exact expectation applied

The unchanged source check proves that the demonstrated string/object difference did not require modifying the database value. It does not establish where an unknown production value originally came from, whether an upstream producer intentionally serialized it, or whether historical consumers rely on the existing string representation.

Keep this accounting separate from insert accounting. Refonte Learning’s guide to reconciling SQLite inputs and outcomes examines whether candidate inputs actually became committed rows under conflict handling. This laboratory does not perform that workflow; its observed population is eight construction records plus six admission controls, not fourteen inserted rows or fourteen served requests.

That distinction also affects failure handling. Do not report “14 records processed” if a future run emitted only thirteen rows and raised on the fourteenth. Capture the missing case and the exception. Do not count only successful constructions and discard rejected admission controls, because the rejection behavior is part of the contract.

The same rule applies to unexpected changes. If a version, adapter, text_factory, SQL rewrite, or source representation differs from the qualified setup, record the changed precondition before interpreting the result. The bounded experiment is useful precisely because it keeps provenance attached to every observation.

Choose a constructor repair or hold the affected output

The operational decision should follow intent, not appearance.

For the fixed eight-case corpus, the object-producing qualified routes are J02, J04, J05, and J08. Their acceptance is bounded to the fixture’s exact source and oracle.

J01, J03, J06, and J07 are not evidence that SQLite malfunctioned. Their quoted results follow the documented argument semantics. When an object contract selected one of those routes, the appropriate action is REPAIR_CONSTRUCTION.

The -> route is not a universal replacement for json(). J05 is appropriate because the source is already an envelope and $.payload is known to select the intended object. Choose it only when that source/path contract matches the application.

Likewise, do not turn “use json() explicitly” into a universal performance or security claim. The point is semantic explicitness at the tested boundary, not a benchmark result.

Recover only from a source whose meaning is known

If output has been constructed with the wrong nested type, regeneration is safer than indiscriminate textual unescaping when the preserved source and intended contract are known.

For this laboratory, the trusted source is RAW, and J02 or J04 shows that an object-valued envelope can be regenerated without rewriting the stored TEXT. In a production workflow, the corresponding proposal would be to repair the owned constructor, regenerate only the bounded affected output from known source material, and re-run the actual consumer contract checks before publication.

Do not globally transform historical strings merely because they contain braces and quotes. Some may intentionally be strings. Some may not be JSON. Some may have a different schema. Some may lack enough provenance to establish intent. Those cases are HOLD, not automatic repair.

Rollback for the demonstrated class of change should also remain proportionate. The proposed rollback is to revert the owned query or configuration change and hold newly invalid output while the discrepancy is investigated. This article does not claim to have executed a durable migration, network replay, consumer rollback, historical-data rewrite, or production deployment.

Assign ownership of the query and its regression evidence

A stable contract needs named ownership across the boundaries this fixture exposes.

The producer maintainer owns the SQL expression that constructs the envelope and the declared serialization intent. If payload is supposed to be an object, this owner should be able to point to the expression that guarantees the object-producing route in the qualified case (such as json(?), json(payload), or a suitable direct -> extraction) and to the evidence proving the emitted type.

The schema or interface owner defines what “object” means beyond the root type: required fields, their types, optional fields, additional-property policy, schema versions, semantic constraints, and migration expectations. The shape gate in this article cannot substitute for that contract.

The consumer owner proves that normal consumer field access is compatible with the emitted document. A producer-side json_type() result is valuable evidence, but it does not exercise a Python, JavaScript, Go, Java, or other downstream consumer. The consumer owner should verify the real interpretation without adding a hidden “decode again if string looks like JSON” workaround unless that behavior is deliberately part of the interface.

The reviewer approves the evidence set rather than only the happy-path SQL. A useful regression corpus retains all fourteen cases: eight construction paths and six shape-admission controls. Keeping J01, J03, J06, and J07 matters because they prove the test can still detect ordinary-text construction. Keeping J10 through J14 matters because a gate that accepts only J09 should continue rejecting valid nonobject roots, malformed JSON, and the wrong Python input type.

Regression evidence should retain exact case IDs and exact expected structures. If someone changes:

json_object('payload', json(?))

to:

json_object('payload', ?)

a reviewer should see J02 change from object to text, not merely a new aggregate pass count.

Record Python and SQLite runtime versions on every bounded rerun. Python documents that sqlite3.sqlite_version is the runtime SQLite version, not the version of the Python module itself. Record connection configuration too: this experiment assumes an owned :memory: connection, autocommit=True, default adapters for the tested primitive values, and default text_factory.

Changes in adapters, converters, text_factory, SQL expression layout, SQLite runtime, Python runtime, or data provenance are triggers to repeat the local matrix and decide whether additional integration checks are necessary. They are not proof that behavior changed; they are reasons to re-establish the bounded evidence.

Stop qualification when preconditions change unexpectedly, a required case is missing, an output has an unanticipated nested type, the source equality check fails, or a diagnostic raises an error not represented by the expected result. Parsing successfully is never enough reason to redefine the expected contract after the fact.

The scope boundary remains explicit: this corpus does not qualify ORM mutation detection, identifier affinity conversion, large-number precision, duplicate-name detection, JSONB, JSON5 admission, subtype behavior through CTEs or subqueries, aggregation or ordering, network APIs, production-data repair, custom adapters, or deployment recovery. Those are separate integration contracts.

Strengthen the database foundations behind reliable integration

The operational conclusion is narrow and auditable: locate the expression supplying json_object()’s value argument, determine whether that expression is ordinary SQL text or a directly recognized JSON-producing expression, and prove the resulting $.payload type before accepting the envelope. SQLite documents that ordinary text can be quoted even when it looks like JSON, while direct JSON-function and -> results receive structured treatment.

For the supplied corpus, J02, J04, J05, and J08 meet the declared object contract and receive ACCEPT_OBJECT. J01, J03, J06, and J07 remain valid JSON but contain string-valued payloads, so an object contract requires REPAIR_CONSTRUCTION. The admission controls preserve distinct REJECT_INPUT_TYPE, REJECT_INVALID_JSON, and REJECT_ROOT_TYPE boundaries. Unknown historical intent remains HOLD.

The supplied commissioning evidence reports PASS_14_CASES for CPython 3.12.14 with SQLite 3.53.1 and the stated source digest. That means the independent expectations matched, including deliberate rejection cases; it does not certify unrelated environments or consumer integrations.

For readers who want broader grounding in database design, SQL query optimization, backup and recovery, performance tuning, security, cloud database management, data migration and integration, and monitoring, Refonte Learning’s Database Administrator Essentials page lists a three-month program at 12–14 hours per week, personalized mentorship, and successful-completion certificates; its published curriculum names platforms including MySQL Workbench, Oracle SQL Developer, AWS RDS, Azure SQL, and Google Cloud Spanner. The page does not establish that this SQLite JSON laboratory is part of the syllabus.