Alan V Comments - Answer Overflow

Alan V

•Created by rphlmr ⚡ on 4/1/2023 in #help

Custom SQL function (json_agg & json_build_object)

Very helpful, I divided it in two so I could nest things up a bit!

export function jsonbAgg(expression: SQL) {
  return sql`jsonb_agg(${expression})`
}

/**
 * @param shape Potential for SQL injections, so you shouldn't allow user-specified key names
 */
export function jsonbBuildObject<T extends Record<string, PgColumn | SQL>>(shape: T) {
  const chunks: SQL[] = []

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

  return sql`jsonb_build_object(${sql.join(chunks)})`
}

export function jsonbAgg(expression: SQL) {
  return sql`jsonb_agg(${expression})`
}

/**
 * @param shape Potential for SQL injections, so you shouldn't allow user-specified key names
 */
export function jsonbBuildObject<T extends Record<string, PgColumn | SQL>>(shape: T) {
  const chunks: SQL[] = []

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

  return sql`jsonb_build_object(${sql.join(chunks)})`
}

... so I can do dumb stuff like this:

db.select({
  id: file.id,
  createdAt: file.createdAt,
  status: file.status,
  streetName: file.streetName,
  auctionDate: file.auctionDate,
  auctionResultsText: file.auctionResultsText,
  auctionResultsSentAt: file.auctionResultsSentAt,
  winningBids: jsonbAgg(
    jsonbBuildObject({
      id: bid.id,
      interestRate: bid.interestRate,
      pricing: bid.pricing,
      monthlyPI: bid.monthlyPI,
      loanProduct: jsonbBuildObject({
        shortName: loanProduct.shortName,
        longName: loanProduct.longName,
        type: loanProduct.type,
        termYears: loanProduct.termYears,
        description: loanProduct.description
      })
    })
  ).as('bids')
})
.from(file)
.leftJoin(bid, and(eq(bid.fileId, file.id), bid.winner))
.leftJoin(loanProduct, eq(bid.loanProductId, loanProduct.id))
.orderBy(file.createdAt)
.groupBy(file.id)

db.select({
  id: file.id,
  createdAt: file.createdAt,
  status: file.status,
  streetName: file.streetName,
  auctionDate: file.auctionDate,
  auctionResultsText: file.auctionResultsText,
  auctionResultsSentAt: file.auctionResultsSentAt,
  winningBids: jsonbAgg(
    jsonbBuildObject({
      id: bid.id,
      interestRate: bid.interestRate,
      pricing: bid.pricing,
      monthlyPI: bid.monthlyPI,
      loanProduct: jsonbBuildObject({
        shortName: loanProduct.shortName,
        longName: loanProduct.longName,
        type: loanProduct.type,
        termYears: loanProduct.termYears,
        description: loanProduct.description
      })
    })
  ).as('bids')
})
.from(file)
.leftJoin(bid, and(eq(bid.fileId, file.id), bid.winner))
.leftJoin(loanProduct, eq(bid.loanProductId, loanProduct.id))
.orderBy(file.createdAt)
.groupBy(file.id)

31 replies

Gaming

Programming