Drizzle Team•13mo ago

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

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

3 Replies

azteca69OP•13mo ago

I actually found a way to filter them, but I am not sure if this is the best way

 ctx.db
      .select({
        ...getTableColumns(organizers),
        ratings: {
          rating: sql<number>`AVG(value)`,
          reviews: sql<number>`COUNT(value)`,
        },
        pictures: sql<(typeof files.$inferSelect)[]>`
        IF(COUNT(${files.id}) > 0, 
         JSON_ARRAYAGG(
           JSON_OBJECT("id", ${files}.id, "name", ${files}.name, "url", ${files.url})
            ),
         NULL)`.as("pictures"),
      })
      .from(organizers)
      .leftJoin(files, eq(organizers.id, files.entityId))
      .leftJoin(ratings, eq(organizers.id, ratings.organizerId))
      .groupBy(organizers.id);

 ctx.db
      .select({
        ...getTableColumns(organizers),
        ratings: {
          rating: sql<number>`AVG(value)`,
          reviews: sql<number>`COUNT(value)`,
        },
        pictures: sql<(typeof files.$inferSelect)[]>`
        IF(COUNT(${files.id}) > 0, 
         JSON_ARRAYAGG(
           JSON_OBJECT("id", ${files}.id, "name", ${files}.name, "url", ${files.url})
            ),
         NULL)`.as("pictures"),
      })
      .from(organizers)
      .leftJoin(files, eq(organizers.id, files.entityId))
      .leftJoin(ratings, eq(organizers.id, ratings.organizerId))
      .groupBy(organizers.id);

It would be really helpful if someone can clarify if this is the right approach drizzle

Angelelz•13mo ago

Yes, that's one way to do it Something that might help is seeing how a findMany query from the RQB is generated

azteca69OP•12mo ago

sql

Gaming

Programming

How to filter null values from json_arrayagg