DT
Drizzle Team•14mo 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•14mo 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•14mo ago
Thank you!
Angelelz
Angelelz•14mo 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•14mo ago
Angelelz
Angelelz•14mo ago
Another BTW: the RQB api does this for you if you have your relations properly defined
Angelelz
Angelelz•14mo ago
Want results from more Discord servers?
Add your server