Search code examples
prisma

With Prisma, how to perform a distinct over a specific field of a json column

With Prisma, how can I perform a distinct over a specific field of a column of type json? For instance, the json column is named data and I have to make a distinct on a field named code.

Solution

  • Prisma does not directly support distinct operations on JSON fields yet.

    However, you can still achieve what you want using raw SQL queries through prisma.$queryRaw or prisma.$executeRaw methods (Reference)

    It could look something like this:

    const result = await prisma.$queryRaw`
    SELECT DISTINCT data->>'code' as code
    FROM YourTableName;
`;