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,
I'm trying to filter the posts based on it's categories. This is what I have
Helpers:
Current query:
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"}]'`
)