Support json_object

[seanwoodward]

Suggesting supporting json_object function. My use case pulls several single rows from multiple related tables, with parent - child and parent - child - grandchild relationships. Initially I approached the structured query using With but ran into problems with column collisions. Pulling child relationships as json_objects allows the composition of related tables without encountering column collisions.

Models

@Table
struct Parent: Codable {
  let id: Int
  var value: String
}

@Table
struct Child: Codable {
  let id: Int
  let parentId: Int
  var childValue: String
}

@Table("grandchildren")
struct Grandchild: Codable {
  let id: Int
  let childId: Int
  var position: Int
  var grandchildValue: String
}

@Table
struct Common: Codable {
  let id: Int
  var commonValue1: String
  var commonValue2: String
}

@Table
struct ParentCommon: Codable {
  let parentId: Int
  let commonId: Int
}

@Table
struct ChildCommon: Codable {
  let childId: Int
  let commonId: Int
}

extension Child {
  @Selection @Table
  struct WithCommonAndFirstGrandchild: Codable {
    let childId: Int
    @Column(as: _CodableJSONRepresentation<Common>.self)
    let common: Common
    @Column(as: _CodableJSONRepresentation<Grandchild>.self)
    let firstGrandchild: Grandchild
  }
}

Structured Query

With {
  Child.leftJoin(ChildCommon.all) { child, lookup in child.id.eq(lookup.childId) }
    .leftJoin(Common.all) { _, lookup, common in lookup.commonId.eq(common.id) }
    .leftJoin(Grandchild.all) { child, _, _, grandchild in
      child.id.eq(grandchild.childId) && grandchild.position.eq(1)
    }
    .select { child, lookup, common, grandchild in
      Child.WithCommonAndFirstGrandchild.Columns(
        childId: child.id,
        common: common.jsonObject(),
        firstGrandchild: grandchild.jsonObject()
      )
    }
} query: {
  Parent
    .leftJoin(Child.all) { parent, child in parent.id.eq(child.parentId) }
    .leftJoin(Child.WithCommonAndFirstGrandchild.all) { _, child, commonAndFirstGrandChild in
      child.id.eq(commonAndFirstGrandChild.childId)
    }
    .leftJoin(ParentCommon.all) { parent, _, _, lookup in
      parent.id.eq(lookup.parentId)
    }
    .leftJoin(Common.all) { _, _, _, lookup, common in
      lookup.commonId.eq(common.id)
    }
    .select { parent, child, commonAndFirstGrandChild, _, parentCommon in
      (parent, parentCommon, child, commonAndFirstGrandChild)
    }
}

SQL

WITH "withCommonAndFirstGrandchilds" AS (
  SELECT "childs"."id" AS "childId", CASE WHEN ("commons"."id" IS NOT NULL) THEN json_object('id', json_quote("commons"."id"), 'commonValue1', json_quote("commons"."commonValue1"), 'commonValue2', json_quote("commons"."commonValue2")) END AS "common", CASE WHEN ("grandchildren"."id" IS NOT NULL) THEN json_object('id', json_quote("grandchildren"."id"), 'childId', json_quote("grandchildren"."childId"), 'position', json_quote("grandchildren"."position"), 'grandchildValue', json_quote("grandchildren"."grandchildValue")) END AS "firstGrandchild"
  FROM "childs"
  LEFT JOIN "childCommons" ON ("childs"."id" = "childCommons"."childId")
  LEFT JOIN "commons" ON ("childCommons"."commonId" = "commons"."id")
  LEFT JOIN "grandchildren" ON (("childs"."id" = "grandchildren"."childId") AND ("grandchildren"."position" = 1))
)
SELECT "parents"."id", "parents"."value", "commons"."id", "commons"."commonValue1", "commons"."commonValue2", "childs"."id", "childs"."parentId", "childs"."childValue", "withCommonAndFirstGrandchilds"."childId", "withCommonAndFirstGrandchilds"."common", "withCommonAndFirstGrandchilds"."firstGrandchild"
FROM "parents"
LEFT JOIN "childs" ON ("parents"."id" = "childs"."parentId")
LEFT JOIN "withCommonAndFirstGrandchilds" ON ("childs"."id" = "withCommonAndFirstGrandchilds"."childId")
LEFT JOIN "parentCommons" ON ("parents"."id" = "parentCommons"."parentId")
LEFT JOIN "commons" ON ("parentCommons"."commonId" = "commons"."id")

see jsonobject-function branch on my fork.