Is there any way in prisma to aggregate a value from a json column? I basically want to build this sql query with prisma:

select
    user_id,
    sum(coalesce((properties->>'duration')::integer, 0)) as connection_duration
from
    events
where
    doc_id = '123'
    and event = 'disconnect'
group by
    user_id

I guess an interface I would want is something like this, but I think prisma would need to know more about the types in the json to be able to give it back to me:

const events = await this.prisma.event.groupBy({
  by: ['userId'],
  where: {
    docId: '123',
    event: 'disconnect',
  },
  _sum: {
    'properties->>duration': true
  },
})

Are there any reasonable workaround here, or do I just need to use a raw query?

Hey @Travis Beck 👋 in this case because Prisma would need to know more about the contents of the JSON I think your best bet is to use a raw query.

That’s what I ended up doing