jsonObject() and jsonGroupArray() flatten nested Codable types instead of preserving their structure

[seanwoodward]

Description

Summary

When calling jsonObject() or jsonGroupArray() on a @Table type that contains a nested Codable struct, the generated SQL flattens all columns into a single json_object level. This produces JSON that does not match the structure Codable expects when decoding, causing a failure with "The data couldn't be read because it is missing."

Root Cause

Both functions use Self.allColumns to enumerate the columns to serialize. allColumns returns the flat SQLite columns (e.g. bucket, value, createdAt, updatedAt), with no awareness that createdAt and updatedAt belong to a nested Swift property (timestamps: Timestamps). The generated JSON therefore has the wrong shape for Codable to decode.

Checklist

• I have determined whether this bug is also reproducible in a vanilla SwiftUI project.
• If possible, I've reproduced the issue using the main branch of this package.
• This issue hasn't been addressed in an existing GitHub issue or discussion.

Expected behavior

Both functions should mirror the Codable structure, nesting createdAt/updatedAt under a timestamps key:

-- jsonObject()
SELECT json_object(
  'bucket', json_quote("timestampedItems"."bucket"),
  'value',  json_quote("timestampedItems"."value"),
  'timestamps', json_object(                                -- ✅ nested key
    'createdAt', json_quote("timestampedItems"."createdAt"),
    'updatedAt', json_quote("timestampedItems"."updatedAt")
  )
)
FROM "timestampedItems"

-- jsonGroupArray()
SELECT json_group_array(json_object(
  'bucket', json_quote("timestampedItems"."bucket"),
  'value',  json_quote("timestampedItems"."value"),
  'timestamps', json_object(                                -- ✅ nested key
    'createdAt', json_quote("timestampedItems"."createdAt"),
    'updatedAt', json_quote("timestampedItems"."updatedAt")
  )
))
FROM "timestampedItems"
GROUP BY "timestampedItems"."bucket"

Both round-trip correctly through Codable and return the expected decoded values.

Actual behavior

Both functions flatten the nested timestamps struct into the top-level json_object:

-- jsonObject()
SELECT json_object(
  'bucket', json_quote("timestampedItems"."bucket"),
  'value',  json_quote("timestampedItems"."value"),
  'createdAt', json_quote("timestampedItems"."createdAt"),  -- ❌ wrong level
  'updatedAt', json_quote("timestampedItems"."updatedAt")   -- ❌ wrong level
)
FROM "timestampedItems"

-- jsonGroupArray()
SELECT json_group_array(json_object(
  'bucket', json_quote("timestampedItems"."bucket"),
  'value',  json_quote("timestampedItems"."value"),
  'createdAt', json_quote("timestampedItems"."createdAt"),  -- ❌ wrong level
  'updatedAt', json_quote("timestampedItems"."updatedAt")   -- ❌ wrong level
))
FROM "timestampedItems"
GROUP BY "timestampedItems"."bucket"

Both fail to decode: "The data couldn't be read because it is missing."

Reproducing project

Define a @table type with a nested struct stored via Codable:

@Table
struct TimestampedItem: Codable {
  var bucket: Int
  var value: Int
  var timestamps: Timestamps  // nested type
}

struct Timestamps: Codable {
  var createdAt: Date
  var updatedAt: Date
}

jsonObject()

TimestampedItem.all.select { $0.jsonObject() }

jsonGroupArray()

TimestampedItem
  .group(by: \.bucket)
  .select {
    GroupedTimestampedItem.Columns(
      bucket: $0.bucket,
      timestampedItems: $0.jsonGroupArray()
    )
  }

swift-structured-queries_jsonObject-and-jsonGroupArray-with-nested-types.patch

Structured Queries version information

0.33.3

Destination operating system

macOS 26.5.2 (25F84)

Xcode version information

Version 26.6 (17F113)

Swift Compiler version information

swift-driver version: 1.148.6 Apple Swift version 6.3.3 (swiftlang-6.3.3.1.3 clang-2100.1.1.101)
Target: arm64-apple-macosx26.0

[seanwoodward]

Missed this pull request #322

[seanwoodward]

After comparing the issue with pull request #322, the problem persists and fails the precondition check in SQLiteQueryDecoder during testing of jsonObject().

[mbrandonw]

Hi @seanwoodward, this is a known issue, and the PR you referenced has a withKnownIssue test to capture the behavior.

We are not currently sure how to support this functionality, and it may even need to wait for 1.0 because it may require a more robust schema description (right now all columns are flattened into an array, but that may need to be a nested structure). For the time being I would just avoid using jsonObject/jsonGroupArray when using nested fields.

[stephencelis]

@seanwoodward We're not planning to rush out the current implementation, but wanna point to #342 and let us know if that fixes things for you?