DT
Drizzle Team•2y ago
Alexandru Comanescu

Postgres json_agg

How can I create this equivalent using drizzle? 
select
  p.id,
  p.description,
  p.name,
  json_build_object(
    'id',
    b.id,
    'name',
    b.name
  ) as brand,
  json_agg(
    json_build_object(
      'id',
      v.id,
      'name',
      v.name
    )
  ) as variant
from
  product as p
  inner join brand as b on b.id = p.brand_id
  inner join product_variant as v on v.product_id = p.id
group by p.id, b.id
select
  p.id,
  p.description,
  p.name,
  json_build_object(
    'id',
    b.id,
    'name',
    b.name
  ) as brand,
  json_agg(
    json_build_object(
      'id',
      v.id,
      'name',
      v.name
    )
  ) as variant
from
  product as p
  inner join brand as b on b.id = p.brand_id
  inner join product_variant as v on v.product_id = p.id
group by p.id, b.id
I cannot find any article in the docs.
6 Replies
Angelelz
Angelelz•2y ago
The syntax is very close, you don't really need the alias but if you want you can define them before: 
const p = alias(product, "p");
const b = alias(brand, "b");
const v = alias(product_variant, "v");
db.select({
  id: p.id,
  description: p.description,
  name: p.name,
  brand: sql`json_build_object('id', ${b.id}, 'name', ${b.name})`.as("brand"),
  variant: sql`json_agg(json_build_object('id', ${v.id}, 'name', ${v.name}))`.as("variant"),
})
  .from(p)
  .innerJoin(b, eq(b.id, p.brand_id))
  .innerJoin(v, eq(v.product_id, p.id))
  .groupBy(p.id, b.id)
const p = alias(product, "p");
const b = alias(brand, "b");
const v = alias(product_variant, "v");
db.select({
  id: p.id,
  description: p.description,
  name: p.name,
  brand: sql`json_build_object('id', ${b.id}, 'name', ${b.name})`.as("brand"),
  variant: sql`json_agg(json_build_object('id', ${v.id}, 'name', ${v.name}))`.as("variant"),
})
  .from(p)
  .innerJoin(b, eq(b.id, p.brand_id))
  .innerJoin(v, eq(v.product_id, p.id))
  .groupBy(p.id, b.id)
Alexandru Comanescu
Alexandru ComanescuOP•2y ago
Thank you!
Angelelz
Angelelz•2y ago
I'd suggest you also use the .mapWith() method on the sql operator to parse the json you'll get from the driver and give it the correct type I believe the way it is right now, brand and variant will be inferred as unknown
Angelelz
Angelelz•2y ago
Angelelz
Angelelz•2y ago
Another BTW: the RQB api does this for you if you have your relations properly defined
Angelelz
Angelelz•2y ago

Did you find this page helpful?