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})`;
}

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}, ...))`

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}))`;
}

Any idea ?

It's used like that:

.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 😄

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?

the resulting SQL looks like that:

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}))`

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.

yeah it's more "easy" than to reduce results later :p

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;

(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).

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)}))`;
}

don't tell me there was an easier way, please 🤣

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

name() produces double " 😢

But you're escaping a column name, no?

It should be double quotes

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);

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 😅

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

Thank you! I now have everything I need to continue my journey