DT
Drizzle Team•7mo ago
Gary, el Pingüino Artefacto

Filtering a jsonb with the shape Array<{id:string,name:string}>

Hi. I have a query that returns all the posts with it's categories like this, 
type Posts = {
  id: string
  title: string
  categories: { id: string; name: string }[]
}[]
type Posts = {
  id: string
  title: string
  categories: { id: string; name: string }[]
}[]
I'm trying to filter the posts based on it's categories. This is what I have Helpers: 
function jsonBuildObject<T extends SelectedFields>(shape: T) {
  const chunks: SQL[] = []

  Object.entries(shape).forEach(([key, value]) => {
    if (chunks.length > 0) {
      chunks.push(sql.raw(`,`))
    }

    chunks.push(sql.raw(`'${key}',`))

    // json_build_object formats to ISO 8601 ...
    if (is(value, PgTimestampString)) {
      chunks.push(sql`timezone('UTC', ${value})`)
    } else {
      chunks.push(sql`${value}`)
    }
  })

  return sql<SelectResultFields<T>>`coalesce(json_build_object(${sql.join(chunks)}), '{}')`
}

function jsonAggBuildObject<T extends SelectedFields, Column extends AnyColumn>(
  shape: T,
  options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } }
) {
  return sql<SelectResultFields<T>[]>`coalesce(jsonb_agg(${jsonBuildObject(shape)}${
    options?.orderBy
      ? sql`order by ${options.orderBy.colName} ${sql.raw(options.orderBy.direction)}`
      : undefined
  }), '${sql`[]`}')`
}

function coalesce<T>(value: SQL.Aliased<T> | SQL<T>, defaultValue: SQL) {
  return sql<T>`coalesce(${value}, ${defaultValue})`
}
function jsonBuildObject<T extends SelectedFields>(shape: T) {
  const chunks: SQL[] = []

  Object.entries(shape).forEach(([key, value]) => {
    if (chunks.length > 0) {
      chunks.push(sql.raw(`,`))
    }

    chunks.push(sql.raw(`'${key}',`))

    // json_build_object formats to ISO 8601 ...
    if (is(value, PgTimestampString)) {
      chunks.push(sql`timezone('UTC', ${value})`)
    } else {
      chunks.push(sql`${value}`)
    }
  })

  return sql<SelectResultFields<T>>`coalesce(json_build_object(${sql.join(chunks)}), '{}')`
}

function jsonAggBuildObject<T extends SelectedFields, Column extends AnyColumn>(
  shape: T,
  options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } }
) {
  return sql<SelectResultFields<T>[]>`coalesce(jsonb_agg(${jsonBuildObject(shape)}${
    options?.orderBy
      ? sql`order by ${options.orderBy.colName} ${sql.raw(options.orderBy.direction)}`
      : undefined
  }), '${sql`[]`}')`
}

function coalesce<T>(value: SQL.Aliased<T> | SQL<T>, defaultValue: SQL) {
  return sql<T>`coalesce(${value}, ${defaultValue})`
}
Current query: 
const sb = db.$with("sb").as(
  db
    .select({
      ...getTableColumns(posts),
      categories: jsonAggBuildObject({
        id: categories.id,
        name: categories.name,
      }).as("categories"),
    })
    .from(posts)
    .leftJoin(postTags, eq(postTags.postId, posts.id))
    .leftJoin(tags, eq(tags.id, postTags.tagId))
    .groupBy(posts.id)
)

const postsWithCategories = await db
  .with(postsQuery)
  .select()
  .from(postsQuery)
  .where(
    sql`${postsQuery.tags} @> '[{"id": "098b9acb-694d-4c00-b87f-64f9811f8810"},{"id":"46bba9b2-9b50-4e70-9284-6cddb2fe32d4"}]'`
  )
const sb = db.$with("sb").as(
  db
    .select({
      ...getTableColumns(posts),
      categories: jsonAggBuildObject({
        id: categories.id,
        name: categories.name,
      }).as("categories"),
    })
    .from(posts)
    .leftJoin(postTags, eq(postTags.postId, posts.id))
    .leftJoin(tags, eq(tags.id, postTags.tagId))
    .groupBy(posts.id)
)

const postsWithCategories = await db
  .with(postsQuery)
  .select()
  .from(postsQuery)
  .where(
    sql`${postsQuery.tags} @> '[{"id": "098b9acb-694d-4c00-b87f-64f9811f8810"},{"id":"46bba9b2-9b50-4e70-9284-6cddb2fe32d4"}]'`
  )
1 Reply
Gary, el Pingüino Artefacto
Gary, el Pingüino ArtefactoOP•7mo ago
Idea: 
async function getPostsByTagIds(tagIds: string[]) {

  /*
    Parameterize '[{"id": "098b9acb-694d-4c00-b87f-64f9811f8810"},{"id":"46bba9b2-9b50-4e70-9284-6cddb2fe32d4"}]'
    Into something like:  
    const filter = `[${tagIds.map(id => ({ id }))}]`
  */

  return await db
  .with(postsQuery)
  .select()
  .from(postsQuery)
  .where(
    sql`${postsQuery.tags} @> SOME MAGIC HERE`
  )
}
async function getPostsByTagIds(tagIds: string[]) {

  /*
    Parameterize '[{"id": "098b9acb-694d-4c00-b87f-64f9811f8810"},{"id":"46bba9b2-9b50-4e70-9284-6cddb2fe32d4"}]'
    Into something like:  
    const filter = `[${tagIds.map(id => ({ id }))}]`
  */

  return await db
  .with(postsQuery)
  .select()
  .from(postsQuery)
  .where(
    sql`${postsQuery.tags} @> SOME MAGIC HERE`
  )
}
This doesn't work btw 
async function getPostsByTagIds(tagIds: string[]) {
  const ids = '[{"id": "098b9acb-694d-4c00-b87f-64f9811f8810"},{"id":"46bba9b2-9b50-4e70-9284-6cddb2fe32d4"}]'
  return await db
  .with(postsQuery)
  .select()
  .from(postsQuery)
  .where(
    sql`${postsQuery.tags} @> ${ids}`
  )
}
async function getPostsByTagIds(tagIds: string[]) {
  const ids = '[{"id": "098b9acb-694d-4c00-b87f-64f9811f8810"},{"id":"46bba9b2-9b50-4e70-9284-6cddb2fe32d4"}]'
  return await db
  .with(postsQuery)
  .select()
  .from(postsQuery)
  .where(
    sql`${postsQuery.tags} @> ${ids}`
  )
}
I ended up using this workaround, may be there is a better way 
async function getPostsByTagIds(tagIds: string[]) {
  const ids = z.string().uuid().array().parse(tagIds)
  const obj = JSON.stringify(ids.map((id) => ({ id })))
  return await db
  .with(postsQuery)
  .select()
  .from(postsQuery)
  .where(
    sql`${postsQuery.tags} @> '${sql.raw(obj)}'`
  )
}
async function getPostsByTagIds(tagIds: string[]) {
  const ids = z.string().uuid().array().parse(tagIds)
  const obj = JSON.stringify(ids.map((id) => ({ id })))
  return await db
  .with(postsQuery)
  .select()
  .from(postsQuery)
  .where(
    sql`${postsQuery.tags} @> '${sql.raw(obj)}'`
  )
}
Want results from more Discord servers?
Add your server