Postgres "json" column type throws errors with JSON filtering's array_contains

[matthewmueller]

Bug description

When trying to select within an array of items, I get the following error

PrismaClientUnknownRequestError3 [PrismaClientUnknownRequestError]: 
Invalid `prisma.users.findMany()` invocation:


  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42883"), message: "operator does not exist: json @> unknown", detail: None, hint: Some("No operator matches the given name and argument types. You might need to add explicit type casts."), position: Some(Original(128)), where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_oper.c"), line: Some(731), routine: Some("op_error") }) }) })
    at cb (/Users/m/dev/src/github.com/prisma/json-nullability/node_modules/@prisma/client/runtime/index.js:36067:17)
    at processTicksAndRejections (internal/process/task_queues.js:93:5) {
  clientVersion: '2.30.0'
}

How to reproduce

Given the following Postgres Schema

CREATE TABLE users (
    id serial PRIMARY KEY,
    cart json DEFAULT '[]'::json
);

I've seeded this schema with the following:

INSERT INTO "public"."users"("id","cart")
VALUES
(1,E'{"items": ["carrots", "peas"]}'),
(2,E'{"items": [null]}'),
(3,E'{"items": ["apples"]}');

I then ran npx prisma db pull to get the following Prisma Schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["filterJson"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model users {
  id   Int   @id @default(autoincrement())
  cart Json? @default("[]") @db.Json
}

Then I ran npx prisma generate and wrote the following script, where I'd like to select the row with the array that contains "carrots":

import { PrismaClient } from "@prisma/client"
const prisma = new PrismaClient()

async function main() {
  const results = await prisma.users.findMany({
    where: {
      cart: {
        path: ["items"],
        array_contains: "carrots",
      },
    },
  })
  console.log(JSON.stringify(results))
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect())

Expected behavior

I'd expect to get back the following result:

[{"id":1,"cart":{"items":["carrots","peas"]}}]

You're able to get back this result by changing the column to jsonb:

ALTER TABLE "public"."users" ALTER COLUMN "cart" TYPE jsonb;
ALTER TABLE "public"."users" ALTER COLUMN "cart" SET DEFAULT '[]'::jsonb;

And running the script again

Prisma information

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["filterJson"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model users {
  id   Int   @id @default(autoincrement())
  cart Json? @default("[]") @db.Json
}

Environment & setup

• OS: OSX
• Database: Postgres
• Node.js version: v14.16.0

Prisma Version

Environment variables loaded from .env
prisma                : 2.30.0
@prisma/client        : 2.30.0
Current platform      : darwin
Query Engine (Binary) : query-engine 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine      : migration-engine-cli 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine  : introspection-core 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary         : prisma-fmt 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash  : 60b19f4a1de4fe95741da371b4c44a92f4d1adcb
Studio                : 0.422.0
Preview Features      : filterJson

[pantharshit00]

I can reproduce this with 2.31.0-dev.37