Quick answer: Test the parsed output of SQLite's json_group_array, including element types, order, and child counts. A query can return valid JSON while quoting objects, inserting a placeholder child, or repeating rows. Each fixture below isolates one of those failures and shows the query shape that avoids it.
These examples use Python's standard library to run SQL against SQLite 3.53.1. Run the blocks in order in one file. The SQLite version linked into your application can differ from the version of a command line client on the same machine.
1. How can you tell whether the array contains objects or quoted JSON?
Decode the result once and check the type of an array element. A JSON array containing a string can look much like an array containing an object in a database console. SQLite treats ordinary text as a string value, even if the text inside it happens to be valid JSON.
import json
import sqlite3
db = sqlite3.connect(":memory:")
def result(sql):
return json.loads(db.execute(sql).fetchone()[0])
assert result("""SELECT json_group_array('{"id":7}')""") == ['{"id":7}']
assert result("SELECT json_group_array(json_object('id',7))") == [{"id": 7}]
The SQLite value argument rules distinguish ordinary SQL text from a value passed directly from another JSON function. Both examples return syntactically valid JSON. The first contains text, and its consumer would need another decode to turn that text into an object. The second gives the consumer a usable object immediately. Add that distinction to your API test; validating syntax alone will let both shapes through.
Paste the synthetic output into the JSON Viewer tree to compare the item with and without quotes. The tree helps locate a string where you expected an expandable object. Keep the actual type assertion in your SQL or application test, where it can catch a regression after a query refactor. For background on the embedded database, see Wikipedia’s SQLite overview.
A useful failure message names the element path and its observed type. That gives the engineer a specific place to look instead of sending them through the whole payload. When a driver returns text, decode it once at the documented boundary. Automatically decoding every string that looks like JSON can turn legitimate string fields into arrays or objects and hide the original query bug.
If you have a failing production response, reduce it to one parent and one child. Start with the expression that creates the child, then compare it with the result after each named query step. Run the final serialization too: a service that sends a database result as the value of another JSON field can quote the entire array, a separate bug from quoting individual children. An assertion at each boundary makes these two failures easy to tell apart.
2. How do you preserve object shape across a CTE boundary?
Treat a JSON object read from a CTE column as text when building the next JSON value. If the column is supposed to hold a serialized document, pass it through json at the point where you aggregate it. Test both the unwrapped and wrapped queries so the distinction stays visible.
cte = "WITH docs AS (SELECT json_object('id',7) AS item) "
assert result(cte + "SELECT json_group_array(item) FROM docs") == ['{"id":7}']
assert result(cte + "SELECT json_group_array(json(item)) FROM docs") == [{"id":7}]
The CTE makes the query easier to read, but naming an expression does not create a durable JSON column type. SQLite's discussion of JSON affinity explains why subtype information should not be treated as a storage contract. Here the explicit conversion belongs on the receiving side of the boundary. If you move the conversion into the CTE and then aggregate its output as plain text, you have moved the conversion away from the consumer that needs it.
Use this technique on fields that are meant to contain JSON documents. A column of customer labels is still text, including a label that happens to be the characters of a JSON object. Conversely, if a document column accepts arbitrary text, adding json may surface a malformed value that was previously quoted without complaint. Decide whether to reject, quarantine, or clean up such rows instead of quietly changing the payload type.
The SQLite JSON storage tests cover related checks at the write boundary. Combining those checks with an aggregation test lets you distinguish an invalid stored value from a valid document that becomes a string only when the export query builds its output.
Choose fixtures that show the difference between the expected object shape and a plain string containing the same characters. An empty array cannot expose this failure because there are no elements to inspect. Keep an empty fixture for the missing child case, and use a populated fixture here. If the document root must be an object, assert that requirement explicitly rather than assuming every value accepted by the json function has the shape the consumer expects.
3. How do you keep the element order stable?
Specify the order inside json_group_array on a SQLite build that supports aggregate ordering. Include a tie breaker when multiple rows can share the same sort key. Verify the resulting sequence and the element type together, because a query can return the right order while still quoting the objects it aggregates.
db.executescript("""
CREATE TABLE parent(id INTEGER PRIMARY KEY);
CREATE TABLE child(id INTEGER PRIMARY KEY, parent_id INTEGER, rank INTEGER);
INSERT INTO parent VALUES (1),(2);
INSERT INTO child VALUES (20,1,1),(10,1,1),(5,1,2);
""")
assert result("""
SELECT json_group_array(json_object('id',id) ORDER BY rank,id)
FROM child
""") == [{"id":10},{"id":20},{"id":5}]
The fixture gives two children the same rank so a query that sorts only by rank has an underspecified order. An outer ORDER BY can place parents in order without ordering the children within each array. The SQLite aggregate documentation describes how the aggregate's own ordering clause controls its inputs. The expected sequence also differs from a simple scan by child ID, which helps expose a missing rank sort.
SQLite's historical sorter discussion documents a case where ordered aggregation could quote objects on older builds. It is a reason to run the test on the version you ship, not a claim that the tested version has that defect. If you support multiple versions, run the same fixture for each one. A test that passes using a developer shell does not establish what a bundled runtime will return.
Order should reflect the product contract. For a ranked list, assert the exact array sequence. For an unordered collection, compare IDs without order in your application tests, but still assert multiplicity and types. The JSON array and object guide explains why an ordered collection belongs in an array rather than relying on object member order.
When reviewing an index change or a rewritten join, run the same ordering fixture again without changing the expected result. A new execution plan may change the order in which rows reach an aggregate that has no ordering clause. Make the tie breaker a field whose uniqueness matches the scope of the collection. If the API allows paging, test pagination separately; ordering the items already selected does not tell you whether the preceding selection chose the right page.
4. How do you return an empty array for a parent without children?
Keep the left join so the parent remains in the result, then filter the aggregate using a child identifier that cannot be null on a real match. That prevents SQLite from aggregating the placeholder row into an object with null fields. Check a parent with children as the positive control.
query = """
SELECT p.id, json_group_array(json_object('id',c.id) ORDER BY c.rank,c.id)
FILTER (WHERE c.id IS NOT NULL)
FROM parent p LEFT JOIN child c ON c.parent_id=p.id
GROUP BY p.id ORDER BY p.id
"""
rows = {pid: json.loads(payload) for pid, payload in db.execute(query)}
assert rows == {1: [{"id":10},{"id":20},{"id":5}], 2: []}
assert result("""
SELECT json_group_array(json_object('id',c.id))
FROM parent p LEFT JOIN child c ON c.parent_id=p.id WHERE p.id=2
""") == [{"id":None}]
The SQLite JSON aggregate reference describes the empty aggregate behavior. A left join preserves the parent by producing a row with null child columns when there is no match. That row is real input to the aggregate unless you filter it. Applying the test in a query level WHERE clause could remove the parent entirely, which is a different failure from returning an array with the wrong element.
Filter on a key that identifies a real child, rather than a field that can legitimately be absent. For instance, filtering on an optional note would drop real children that lack notes. If the application uses null to represent data that has not loaded, define that state separately from an empty collection. The API test should distinguish no parent, an existing parent with an empty array, and an existing child whose optional fields are null. Those cases can render differently in a client even though they all originate from rows containing SQL nulls.
Verify the empty case through the HTTP serializer if this query backs an endpoint. Some applications translate SQL nulls, empty lists, and missing keys differently when constructing the response. A query level assertion localizes the database behavior; a response level assertion makes sure that behavior survives serialization. Use a fixture whose optional child fields are null to test that the filter preserves the row when the identifier is present. That catches filters that accidentally use an optional field.
5. How do you prevent two child collections from multiplying each other?
Aggregate each collection independently before combining them in the parent document. Joining independent child tables first can multiply their rows, so each aggregate receives duplicates. Test with more than one item on each side and verify the exact IDs and counts in the final nested JSON, including the empty case.
db.executescript("""
CREATE TABLE tag(id INTEGER PRIMARY KEY, parent_id INTEGER);
INSERT INTO tag VALUES (90,1),(91,1);
""")
assert db.execute("""
SELECT count(*) FROM child c JOIN tag t USING(parent_id)
WHERE c.parent_id=1
""").fetchone()[0] == 6
for pid, expected in [
(1, {"children":[{"id":10},{"id":20},{"id":5}],"tags":[90,91]}),
(2, {"children":[],"tags":[]}),
]:
row = db.execute("""
SELECT json_object(
'children',json((SELECT json_group_array(json_object('id',c.id)
ORDER BY c.rank,c.id)
FROM child c WHERE c.parent_id=p.id)),
'tags',json((SELECT json_group_array(t.id ORDER BY t.id)
FROM tag t WHERE t.parent_id=p.id)))
FROM parent p WHERE p.id=?
""", (pid,)).fetchone()
assert json.loads(row[0]) == expected
The first query demonstrates the fanout: three children paired with two tags make six rows before aggregation. A test with one row in each collection would miss it. The corrected query reduces each collection to one aggregate result for the parent. The explicit json calls make the contract at the outer object construction clear. This fixture checks output shape; it does not compare the performance of correlated subqueries with aggregating collections ahead of time.
Avoid reaching for DISTINCT as the first repair. It can hide the row multiplication while deleting repeated values that the product actually allows. For example, two separate records might legitimately carry the same label. Test the identity of the records before choosing whether any deduplication is appropriate. On a large data set, inspect the query plan and measure representative cardinalities separately from these small correctness fixtures. The key regression check remains the same: adding one tag should change the tag array without multiplying the children.
For a comparison with other engines, see the PostgreSQL and MySQL JSON guide. Test equivalent query contracts in each engine separately; SQL function names, result types, and rules for nested JSON need not carry across dialects.