DT
Drizzle Team•2y ago
rphlmr ⚡

Custom SQL function (json_agg & json_build_object)

Hello there. I'm new to Drizzle and I love it 😍. I have successfully made a jsonAgg helper. 
function jsonAgg<T extends PgTable<TableConfig>>(table: T) {
  return sql<InferModel<T>[]>`json_agg(${table})`;
}
function jsonAgg<T extends PgTable<TableConfig>>(table: T) {
  return sql<InferModel<T>[]>`json_agg(${table})`;
}
Now I have trouble to make a jsonAggBuildObject 😅 that should produce something like this: 
sql`json_agg(json_build_object('key1', ${table.col1}, 'key2', ${table.col2}, ...))`
sql`json_agg(json_build_object('key1', ${table.col1}, 'key2', ${table.col2}, ...))`
I try something but it end with an error error: could not determine data type of parameter $1 
function jsonAggBuildObject<T extends Record<string, AnyColumn>>(shape: T) {
  const shapeString = Object.entries(shape)
    .map(([key, value]) => {
      return `'${key}', ${value}`;
    })
    .join(",");

  return sql<
    InferColumnsDataTypes<T>[]
  >`json_agg(json_build_object(${shapeString}))`;
}
function jsonAggBuildObject<T extends Record<string, AnyColumn>>(shape: T) {
  const shapeString = Object.entries(shape)
    .map(([key, value]) => {
      return `'${key}', ${value}`;
    })
    .join(",");

  return sql<
    InferColumnsDataTypes<T>[]
  >`json_agg(json_build_object(${shapeString}))`;
}
Any idea ?
14 Replies
rphlmr ⚡
rphlmr ⚡OP•2y ago
It's used like that: 
.select({
   ...other,
   themes: jsonAggBuildObject({
      id: themes.id,      
      label: themes.label,
    }),
 })
.select({
   ...other,
   themes: jsonAggBuildObject({
      id: themes.id,      
      label: themes.label,
    }),
 })
Note: if it doesn't optimize the underlying request (selecting only themes.id and themes.label, forget that, I'm fine with the first helper and some mapping 😄
Dan
Dan•2y ago
Your function should return an object, not sql, because that's what .select() accepts. So you need to return key-value pairs, where the key is just to map the result object, and the value is what you want to select for that particular key. No, wait, that's not correct Could you show how you expect the resulting SQL to look like?
rphlmr ⚡
rphlmr ⚡OP•2y ago
the resulting SQL looks like that: 
json_agg(
    json_build_object(
      'id', "themes"."id",
      'label', "themes"."label"
    )
  ) AS "themes"
json_agg(
    json_build_object(
      'id', "themes"."id",
      'label', "themes"."label"
    )
  ) AS "themes"
since my message, I read https://github.com/drizzle-team/drizzle-orm/blob/main/docs/joins.md#aggregating-results 😄 using this "manually" in the select works 😉 But I wanted to explore the "helper" way haha. 
sql`json_agg(json_build_object('id', ${themes.id}, 'label', ${themes.label}))`
sql`json_agg(json_build_object('id', ${themes.id}, 'label', ${themes.label}))`
Dan
Dan•2y ago
OK I see. In that case, you need to escape the column names with the name() function, which will tell the ORM that it needs to be treated as a column name instead of a parameter. When you use name(), you don't need to manually add quotes to the query, it will be done by the ORM. So something like sql`${name(key)}, ${value}` Also, you always need to use sql when you're building the query. You can compose sql parts inside other sql. You might take a look at how we map the selection objects to the SQL ourselves: https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/pg-core/dialect.ts#L121 But this is an interesting helper! We might explore the possibility to add it to the library.
rphlmr ⚡
rphlmr ⚡OP•2y ago
yeah it's more "easy" than to reduce results later :p
Dan
Dan•2y ago
btw, I've experimented a bit with this type of query and here's what I got that works (this example uses cities 1:m users): 
SELECT
    c.id AS city_id,
    c.name,
    count(u.id) as users_count,
    coalesce(json_agg(
        json_build_array(u.id, u.name)
    ) filter (where u.id is not null and u.name is not null), '[]') AS users
FROM
    cities c
left JOIN
    users2 u ON c.id = u.city_id
GROUP BY
    c.id;
SELECT
    c.id AS city_id,
    c.name,
    count(u.id) as users_count,
    coalesce(json_agg(
        json_build_array(u.id, u.name)
    ) filter (where u.id is not null and u.name is not null), '[]') AS users
FROM
    cities c
left JOIN
    users2 u ON c.id = u.city_id
GROUP BY
    c.id;
(just replace the json_build_array with json_build_object) might help you a bit to build your helper 🙂 the joined count is for an edge case when you're joining nullable columns only and no right rows were joined for a certain left row - the result will have a single right row with all columns as nulls, but the joined count will be zero. Also, check the filter clause in json_agg, it needs to have a check that one of non-nullable columns in the joined table is not null (for automated generation, it can just use a first non-nullable column from the joined table).
rphlmr ⚡
rphlmr ⚡OP•2y ago
Thanks for your help! It takes me 5h but it works 😂 
// ⚠️ Potential for SQL injections, so you shouldn't allow user-specified key names
function jsonAggBuildObject<T extends Record<string, AnyColumn>>(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<
    InferColumnsDataTypes<T>[]
  >`json_agg(json_build_object(${sql.fromList(chunks)}))`;
}
// ⚠️ Potential for SQL injections, so you shouldn't allow user-specified key names
function jsonAggBuildObject<T extends Record<string, AnyColumn>>(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<
    InferColumnsDataTypes<T>[]
  >`json_agg(json_build_object(${sql.fromList(chunks)}))`;
}
don't tell me there was an easier way, please 🤣
Dan
Dan•2y ago
1. You shouldn't use inlineParams here, it's only intended for static queries, you're introducing a SQL injection vulnerability. As I wrote earlier, you should use the name() function. 2. [] as SQL[] is an incorrect usage, it should be const chunks: SQL[] = [] Other then that, looks good
rphlmr ⚡
rphlmr ⚡OP•2y ago
name() produces double " 😢
Dan
Dan•2y ago
But you're escaping a column name, no? It should be double quotes
rphlmr ⚡
rphlmr ⚡OP•2y ago
json_build_object is a mapper to make a custom json from a group result. 
db
      .select({
        timelineModel,
        postModel,
        eventModel,
        // here 👇
        themes: jsonAggBuildObject({
          test: themeModel.id,
          test2: themeModel.label,
        }),
      })
      .from(timelineModel)
      .leftJoin(postModel, eq(timelineModel.pub_id, postModel.pub_id))
      .leftJoin(eventModel, eq(timelineModel.pub_id, eventModel.pub_id))
      .leftJoin(
        themesInTimeline,
        eq(themesInTimeline.pub_id, timelineModel.pub_id)
      )
      .leftJoin(themeModel, eq(themeModel.id, themesInTimeline.theme_id))
      .where(inArray(timelineModel.club_id, clubsId))
      .groupBy(timelineModel.pub_id, postModel.pub_id, eventModel.pub_id)
      .orderBy(desc(timelineModel.created_at))
      .limit(20);
db
      .select({
        timelineModel,
        postModel,
        eventModel,
        // here 👇
        themes: jsonAggBuildObject({
          test: themeModel.id,
          test2: themeModel.label,
        }),
      })
      .from(timelineModel)
      .leftJoin(postModel, eq(timelineModel.pub_id, postModel.pub_id))
      .leftJoin(eventModel, eq(timelineModel.pub_id, eventModel.pub_id))
      .leftJoin(
        themesInTimeline,
        eq(themesInTimeline.pub_id, timelineModel.pub_id)
      )
      .leftJoin(themeModel, eq(themeModel.id, themesInTimeline.theme_id))
      .where(inArray(timelineModel.club_id, clubsId))
      .groupBy(timelineModel.pub_id, postModel.pub_id, eventModel.pub_id)
      .orderBy(desc(timelineModel.created_at))
      .limit(20);
name() produces this sql : json_agg(json_build_object("test","themes_2"."id","test2","themes_2"."label")) should be json_agg(json_build_object('test',"themes_2"."id",'test2',"themes_2"."label")) (single quotes for key name) I know that's maybe overthinking 😅
Dan
Dan•2y ago
ahh I see then you can use sql.raw(`'${key}'`) but you should be careful, since it still has potential for SQL injections, so you shouldn't allow user-specified key names
rphlmr ⚡
rphlmr ⚡OP•2y ago
Thank you! I now have everything I need to continue my journey
Alan V
Alan V•9mo ago
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)
Want results from more Discord servers?
Add your server