Incorrect query using jsonGroupArray order clause

[thinkpractice]

Hi there,

I've been trying to use structured-queries to group a parent table ConversationThread with a child table Posts. Post has a foreign key pointing to the ConversationThread. This foreign key can be nil/NULL. I'm using a @Selection struct to group the posts into one conversation:

@Selection
struct PostsThread
{
    let id: String
    let summary: String
    @Column(as: [Post].JSONRepresentation.self)
    let posts: [Post]
}

I'm using a jsonGroupArray to get the separate Post objects and group them into an array. The original query looked like this:

self._thread = FetchOne((ConversationThread.all
            .group (by: \.id)
            .order (by: \.id)
            .where { $0.id.eq(item.id)}
            .join(Post.all) { $1.conversationThreadId.eq($0.id) }
            .select {
                PostsThread.Columns(
                    id: $0.id,
                    summary: $0.summary,
                    posts: $1.jsonGroupArray()
                )
            }
        ))

This works. However, I want to order the posts by their time, hence:

self._thread = FetchOne((ConversationThread.all
            .group (by: \.id)
            .order (by: \.id)
            .where { $0.id.eq(item.id)}
            .join(Post.all) { $1.conversationThreadId.eq($0.id) }
            .select {
                PostsThread.Columns(
                    id: $0.id,
                    summary: $0.summary,
                    posts: $1.jsonGroupArray(order: $1.id.desc())
                )
            }
        ))

This however results in the following incorrect query:

SELECT 
     "conversationThreads"."id" AS "id", 
     "conversationThreads"."summary" AS "summary", 
     json_group_array(
          json_object('id', json_quote("posts"."id"), 
          'username', json_quote("posts"."username"), 
          'handle', json_quote("posts"."handle"), 
          'time', json_quote("posts"."time"), 
          'content', json_quote("posts"."content"), 
          'profileImage', json_quote("posts"."profileImage"), 
          'timeline', json_quote("posts"."timeline"), 
          'conversationThreadId', json_quote("posts"."conversationThreadId")) ORDER BY "posts"."id" DESC) AS "posts" 
FROM "conversationThreads" 
JOIN "posts" ON ("posts"."conversationThreadId" = "conversationThreads"."id") 
WHERE ("conversationThreads"."id" = ?) GROUP BY "conversationThreads"."id" 
ORDER BY "conversationThreads"."id"

And this query crashes. Any idea what's going wrong? Do I call order clause of jsonGroupArray correctly? I've been trying to manually correct the query in a sqlite client, but no luck so far.

[mbrandonw]

Hi @thinkpractice, unfortunately jsonGroupArray(order:) is only supported in SQLite 3.44, which is slightly newer than what is shipped in iOS 18. However, iOS 26 does support this. I think your only option is to sort in memory.

Also this should not cause a crash in our tools. An invalid query should only cause a function to throw. Perhaps you are force try!'ing somewhere?

[thinkpractice]

Ah that explains it, thanks! Will sort in code then, no problem! And indeed I was wrong saying it crashed, the query indeed throws an error.