Json filter, greater than, less than, doing string comparison instead

[njfix6]

Bug description

When trying to grab all objects of a json field that are greater than a specific values, it does a string comparison instead of a value comparison.

How to reproduce

1. Create a json column values
2. Make sure you have the following preview features: previewFeatures = ["orderByRelation", "filterJson"]
3. Try to get that columns back

const entries = await this.prismaClient.accountEntity.findMany({
      where: {
        values: {
          path: ["test"],
          gt: 5,
        },
      },
    });

Expected behavior

I would expect this to return all object where values contains a number greater than 5, but instead it is doing a string comparison.

Prisma information

Environment & setup

• OS: Mac OS
• Database: PostgreSQL
• Node.js version: v15.4.0

Prisma Version

2.25.0

[pantharshit00]

I am unable to reproduce this. Here is the data in the database I used for this reproduction:

Reproduction script:

import { PrismaClient } from "@prisma/client";

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

  prisma.$on("query", (e) => {
    console.log(e.query, e.params);
  });


  const data = await prisma.accountEntity.findMany({
    where: {
      values: {
        path: ["test"],
        gt: 5,
      },
    },
  });
  console.log(data);
  prisma.$disconnect();
}

main();

Result:

SELECT "public"."AccountEntity"."id", "public"."AccountEntity"."values" FROM "public"."AccountEntity" WHERE
(("public"."AccountEntity"."values"#>>ARRAY[$1]::text[]) > $2 AND JSONB_TYPEOF(("public"."AccountEntity"."values"#>ARRAY[$3]::text[])) = $4) OFFSET $5 ["test",5,"test","number",0]
[
  { id: 'three', values: { test: 6 } },
  { id: 'four', values: { test: 7 } }
]
Done in 1.54s.

If you are confusing ARRAY[$1]::text[] in the query with string comparison, that is only used to retrieve object and it is not casting the value used it comparison. It is basically '{"test": 6 }'::json#>>'{test}' which will return 6.

[firmanjml]

@pantharshit00 I did some experiment feature using this on my application and it seems to have issues if the numbers that are far apart from each other for example here price with 50,100

result with input price greater than equal 40

        const data = await this.prisma.listing.findMany({
            select: {
                id: true,
                metadata: true
            },
            where: {
                metadata: {
                    path: ['price'],
                    gte: 40
                },
                type: 1
            }
        });

query

prisma:query SELECT "public"."listing"."id", "public"."listing"."metadata" FROM "public"."listing" WHERE ("public"."listing"."type" = $1 AND (("public"."listing"."metadata"#>>ARRAY[$2]::text[]) >= $3 AND JSONB_TYPEOF(("public"."listing"."metadata"#>ARRAY[$4]::text[])) = $5)) OFFSET $6

result return 1 row which I expect 2 rows with price 100.

[
  {
    "id": "ckqzeri2900138cu1zmbylss6",
    "metadata": {
      "rate": null,
      "price": 50,
      "currency": "SGD"
    }
  }
]

[janpio]

@firmanjml That sounds like a bug - optimally directly create a bug report for that which we can reproduce and check.

[jkomyno]

Hi @njfix6, it looks like this bug was also reported in #8224, which was fixed in prisma@2.30.0. However, I'd encourage you to use a more recent version of Prisma, such as 4.16.2 or 5.0.0. I will close this issue as fixed, but feel free to leave a comment in case you feel this issue persists. Thanks!