Filtering a jsonb with the shape Array<{id:string,name:string}>

type Posts = {
  id: string
  title: string
  categories: { id: string; name: string }[]
}[]

function jsonBuildObject<T extends SelectedFields>(shape: T) {
  const chunks: SQL[] = []

  Object.entries(shape).forEach(([key, value]) => {
    if (chunks.length > 0) {
      chunks.push(sql.raw(`,`))
    }

    chunks.push(sql.raw(`'${key}',`))

    // json_build_object formats to ISO 8601 ...
    if (is(value, PgTimestampString)) {
      chunks.push(sql`timezone('UTC', ${value})`)
    } else {
      chunks.push(sql`${value}`)
    }
  })

  return sql<SelectResultFields<T>>`coalesce(json_build_object(${sql.join(chunks)}), '{}')`
}

function jsonAggBuildObject<T extends SelectedFields, Column extends AnyColumn>(
  shape: T,
  options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } }
) {
  return sql<SelectResultFields<T>[]>`coalesce(jsonb_agg(${jsonBuildObject(shape)}${
    options?.orderBy
      ? sql`order by ${options.orderBy.colName} ${sql.raw(options.orderBy.direction)}`
      : undefined
  }), '${sql`[]`}')`
}

function coalesce<T>(value: SQL.Aliased<T> | SQL<T>, defaultValue: SQL) {
  return sql<T>`coalesce(${value}, ${defaultValue})`
}

const sb = db.$with("sb").as(
  db
    .select({
      ...getTableColumns(posts),
      categories: jsonAggBuildObject({
        id: categories.id,
        name: categories.name,
      }).as("categories"),
    })
    .from(posts)
    .leftJoin(postTags, eq(postTags.postId, posts.id))
    .leftJoin(tags, eq(tags.id, postTags.tagId))
    .groupBy(posts.id)
)

const postsWithCategories = await db
  .with(postsQuery)
  .select()
  .from(postsQuery)
  .where(
    sql`${postsQuery.tags} @> '[{"id": "098b9acb-694d-4c00-b87f-64f9811f8810"},{"id":"46bba9b2-9b50-4e70-9284-6cddb2fe32d4"}]'`
  )