Prisma-client-js: JSON Filter does not work

[MichalLytek]

Bug description

The 2.1.0 release notes claims that "When querying data, you can now perform basic filtering with Json fields using equal and not".

However, it's not true - looks like the filter object is treated as the JSON payload.

prisma:query SELECT "public"."Post"."uuid", "public"."Post"."createdAt", "public"."Post"."updatedAt", "public"."Post"."published", "public"."Post"."title", "public"."Post"."content", "public"."Post"."authorId", "public"."Post"."kind", "public"."Post"."metadata" FROM "public"."Post" WHERE "public"."Post"."uuid" = $1 LIMIT $2 OFFSET $3
{ publishedOnWikiLeaks: false }
prisma:query SELECT "public"."Post"."uuid", "public"."Post"."createdAt", "public"."Post"."updatedAt", "public"."Post"."published", "public"."Post"."title", "public"."Post"."content", "public"."Post"."authorId", "public"."Post"."kind", "public"."Post"."metadata" FROM "public"."Post" WHERE "public"."Post"."metadata"::jsonb = $1 OFFSET $2
[]
prisma:query SELECT "public"."Post"."uuid", "public"."Post"."createdAt", "public"."Post"."updatedAt", "public"."Post"."published", "public"."Post"."title", "public"."Post"."content", "public"."Post"."authorId", "public"."Post"."kind", "public"."Post"."metadata" FROM "public"."Post" WHERE "public"."Post"."metadata"::jsonb = $1 OFFSET $2
[
  {
    uuid: 'f2d47551-366d-4e9f-a437-25a361537603',
    createdAt: 2019-08-17T00:00:00.000Z,
    updatedAt: 2020-06-10T12:19:57.330Z,
    published: false,
    title: 'Post title 2',
    content: 'Content 2',
    authorId: 1,
    kind: 'ADVERT',
    metadata: { publishedOnWikiLeaks: false }
  }
]

How to reproduce

import { PrismaClient } from "./prisma/generated/client";

async function main() {
  const prisma = new PrismaClient({
    log: ["query"],
  });

  const post = await prisma.post.findOne({
    where: {
      uuid: "f2d47551-366d-4e9f-a437-25a361537603",
    },
  });
  console.log(post?.metadata);

  console.log(
    await prisma.post.findMany({
      where: {
        metadata: {
          equals: post?.metadata,
        },
      },
    }),
  );
  console.log(
    await prisma.post.findMany({
      where: {
        metadata: post?.metadata,
      },
    }),
  );

  await prisma.disconnect();
}

main().catch(console.error);

Expected behavior

Properly filter by equal and not equal.

Prisma information

model Post {
  uuid      String    @default(uuid()) @id
  metadata  Json
}

Environment & setup

@prisma/cli          : 2.1.0
Current platform     : debian-openssl-1.1.x
Query Engine         : query-engine 4440772035795a0424be62040e2295c56e5c6ad0 (at /home/majkel/development/typegraphql-prisma/experiments/node_modules/@prisma/cli/query-engine-debian-openssl-1.1.x)
Migration Engine     : migration-engine-cli 4440772035795a0424be62040e2295c56e5c6ad0 (at /home/majkel/development/typegraphql-prisma/experiments/node_modules/@prisma/cli/migration-engine-debian-openssl-1.1.x)
Introspection Engine : introspection-core 4440772035795a0424be62040e2295c56e5c6ad0 (at /home/majkel/development/typegraphql-prisma/experiments/node_modules/@prisma/cli/introspection-engine-debian-openssl-1.1.x)
Format Binary        : prisma-fmt 4440772035795a0424be62040e2295c56e5c6ad0 (at /home/majkel/development/typegraphql-prisma/experiments/node_modules/@prisma/cli/prisma-fmt-debian-openssl-1.1.x)

• OS: WSL Ubuntu 20.04
• Database: PostgreSQL
• Prisma version: 2.1.0
• Node.js version: v14.3.0

[pantharshit00]

I can confirm this.

Reproduction is very detailed. Huge thanks for that. I will add running this SQL statement to make it complete:

INSERT INTO "public"."Post" ("metadata", "uuid") VALUES ('{"test":"test"}', 'f2d47551-366d-4e9f-a437-25a361537603');

[Toub]

Same problem with :

prisma.my_table.findMany({
   where: {
      some_jsonb_column: {
        not: null,
      }
   },
  });

Does not return any result.

[timsuchanek]

Thanks for reporting! This is fixed in 2.1.2.