Drizzle TeamDT
Drizzle Team•3y ago
azteca69

How to filter null values from json_arrayagg

Hey sry if this is a stupid question, I am still kinda new to more complex sql queries.
The problem here is that pictures returns an array of objects that have all properties null. As far as I know this is a correct behaviour for the left join.
I just wanted to know how I could filter the null values in a good way.
 ctx.db
      .select({
        ...getTableColumns(organizers),
        ratings: {
          rating: sql<number>`AVG(value)`,
          reviews: sql<number>`COUNT(value)`,
        },
        pictures: sql<
          (typeof files.$inferSelect)[]
        >`JSON_ARRAYAGG(JSON_OBJECT("id", ${files}.id, "name", ${files}.name, "url", ${files.url}))`.as(
          "pictures",
        ),
      })
      .from(organizers)
      .leftJoin(files, eq(organizers.id, files.entityId))
      .leftJoin(ratings, eq(organizers.id, ratings.organizerId))
      .groupBy(organizers.id);
Was this page helpful?