azteca69
azteca69
PostsComments
DTDrizzle Team
Created by azteca69 on 11/17/2023 in #help
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);
6 replies