jsonAggBuildObject: support sorting

[etx121]

Hello @emmbm ,

Thank you for the helpers, they are really helpful (I mostly use jsonbBuildObject).
I wanted to know if it would be possible to support orderBy for jsonAggBuildObject and JSONB_AGG?
I cannot maintain the sorting when I use jsonAggBuildObject in my code, and I didn't find a way to add orderBy in arguments.

[emmnull]

Hey @etx121, thanks for the feedback! I haven't updated the helpers in a bit but there are plans in the making for renewing them so they better fit drizzle-orm's latest features. It's always a good thing to hear about specific use-cases.

Could you share an sql query snippet or some pseudo-code of what you would expect the helper api to look like?

[etx121]

Hi @emmbm ,

Thanks for the quick reply.
I want to do something like that for example:

SELECT 
  json_agg(
    json_build_object(
      'country_slug', country_slug,
      'country_code', country_code,
      'country_name', country_name,
    )
    ORDER BY country_name ASC
  ) as country_list
FROM countries

But thanks again for the helpers, I think you are the only one to have done something like that, so I can keep the types when having to deal with JSON objects, even though the documentation was a bit tough to apprehend. I am mainly using getColumns and jsonbBuildObject.
Maybe you should suggest to drizzle to integrate more operator like that, by default.
Also, in drizzle, COALESCE is missing, I don't know if you had that in mind to implement?

[emmnull]

Thanks for the snippet, will look into it!

... even though the documentation was a bit tough to apprehend

Yeah, I put the package together kind of half-assedly during a rush of work, but I plan on making things much more user-friendly at some point.

Maybe you should suggest to drizzle to integrate more operator like that, by default.

Yup! The core team and I talked a bit and the plan is to either integrate more of these into the core drizzle-orm package or release a new official helpers package with a proper test suite once they reach a v1.

As for the coalesce, you should already be able to use this one: https://github.com/emmbm/drizzle-orm-helpers/blob/035ffd5af68ebc2773011445cf3a2c753c55a3fb/src/functions.ts#L22-L27

Lmk if it isn't working as expected.

[etx121]

Ok thanks Emmanuel!
I ended up finding a solution with Claude giving me randomly the way to use the function ahah

Hope the drizzle team will push some helpers fast enough 🙏

[etx121]

Hi @emmnull ,

I was still finding a way to sort when aggregating with jsonAgg from here.
With the AI, I found this working solution to sort the results inside the list:

import { sql } from "drizzle-orm";
import type { SQL, SQLWrapper } from "drizzle-orm";
import { InferData } from "drizzle-orm-helpers";

export function jsonAgg<T extends SQLWrapper, N extends boolean = true>(
  selection: T,
  { notNull = true as N, orderBy }: { notNull?: N; orderBy?: any } = {}
): SQL<N extends true ? NonNullable<InferData<T>>[] : InferData<T>[] | [null]> {
  if (notNull) {
    return sql`coalesce(json_agg(${selection}${
      orderBy ? sql.raw(" order by ") : sql.raw("")
    }${
      orderBy ? orderBy : sql.raw("")
    }) filter (where ${selection} is not null), '[]')`;
  }
  return sql`json_agg(${selection}${
    orderBy ? sql.raw(" order by ") : sql.raw("")
  }${orderBy ? orderBy : sql.raw("")})`;
}

So now, we could use it that way:

// jsonbBuildObject is from your existing library
// ... drizzle select
myColumn: jsonAgg(
            jsonbBuildObject({
              id: tableToAggregate.id,
              name: tableToAggregate.name,
            }),
            { orderBy: asc(tableToAggregate.name) }
          ).as("my_column"),

What do you think?

[etx121]

Hi @emmnull ,

We were also talking about COALESCE implementation.
I using this function:

export function coalesce<T extends SQL | SQL.Aliased>(
  first: T,
  ...rest: (SQL | SQL.Aliased)[]
): SQL<InferData<T>> {
  return sql<InferData<T>>`COALESCE(${sql.join([first, ...rest], sql`, `)})`;
}

What do you think?