JSON filtering not working

[Aloysius999]

Bug description

I am attempting to query on the content of a JSON field.

My JSON looks like this:

{
	"instrument":{
		"id":90,
		"description":"Mouth organ",
		"hsNumber":"412.132",
		"primaryClassification":"Aerophone",
		"secondaryClassification":"Free aerophone"
	}
}

My prisma query is this:

const items = await prisma.listingItem.findMany({
    where: {
      itemData: {
        path: "$.instrument.description",
        string_contains: "organ",
      },
    },
  });

The query returns 0 records, whereas I'm expecting more than 1 record from the DB.
'

This is an extract from the Prisma-generated SQL query from the log (string quotes removed for formatting to work):

SELECT mydomain.listingItem.id, mydomain.listingItem.userId, mydomain.listingItem.createdAt, mydomain.listingItem.updatedAt, mydomain.listingItem.itemData FROM mydomain.listingItem WHERE (JSON_UNQUOTE(JSON_EXTRACT(mydomain.listingItem.itemData, ?)) LIKE ? AND (JSON_TYPE(JSON_EXTRACT(mydomain.listingItem.itemData, ?)) = ?))

and the log parameters

Params: ["$.instrument.description","%organ%","$.instrument.description","STRING"]

There appears to be no information about the instrument.description path of the JSON_EXTRACT function in the SQL query.

Playing with the query:

SELECT * FROM mydomain.listingItem
WHERE JSON_UNQUOTE(JSON_EXTRACT(mydomain.listingItem.itemData, '$.instrument.description')) LIKE '%organ%'

returns 0 records

How to reproduce

1. create a DB table listingItem
2. add a record with this JSON data
3. query the record using the prisma query above

I'm using Postman to send the query to the API.

Expected behavior

There is at least 1 record in my DB with the word organ contained in the JSON path instrument.description.

No records are being returned.

I expect matching records to be returned.

Prisma information

My prisma schema looks like this:

model listingItem {
  id     String @id @default(cuid())
  userId String

  createdAt DateTime? @default(now())
  updatedAt DateTime? @updatedAt

  itemData Json?
}

Environment & setup

Windows 10 Pro
Version 10.0.19045 Build 19045

MySQL

node -v
v20.10.0

next: "^14.1.4",

Prisma Version

    "prisma": "^5.12.1",

npm prisma -v
10.2.3

[ludralph]

Hi @Aloysius999 👋

Thank you for raising htis question.

I am not able to reproduce this as JSON filtering "$.instrument.description" correctly worked for me using the same query you shared. You might want to keep an eye out for case sensitivity in the stored record in the DB.

const res = await prisma.listingItem.findMany({
    where: {
      itemData: {
        path: "$.instrument.description",
        string_contains: "organ",
      },
    },
  });

[Aloysius999]

Thanks for the reply.

The SQL query Prisma generates does not contain the path - $.instrument.description - so how can it query specific matching rows.

Having looked at the MySQL JSON_* documentation I believe the Prisma generated query is incorrect. I played around with various alternatives in MySQL Workbench, and this query below works and returns data in the provided path:

SELECT * 
FROM mydomain.listingItem 
WHERE JSON_EXTRACT(JSON_UNQUOTE(mydomain.listingItem.itemData), "$.instrument.description") LIKE "%organ%";

returns all rows containing organ at that JSON path.

Notice that I'm unquoting the JSON before I extract from the provided path - extracting before unquoting doesn't work in MySQL Workbench - it returns 0 rows.

The above SQL query would therefore represent my original Prisma query:

const items = await prisma.listingItem.findMany({
    where: {
      itemData: {
        path: "$.instrument.description",
        string_contains: "organ",
      },
    },
  });

To verify this approach, this SQL query will return the contents of the JSON path:

SELECT JSON_EXTRACT(JSON_UNQUOTE(mydomain.listingItem.itemData), "$.instrument.description") AS Description
FROM mydomain.listingItem;

Turning to the original Prisma-generated SQL query - it doesn't work - what does work from the MySQL Workbench is this (where I've substituted ? for the values in the Prisma query to make it work in MySQL Workbench):

SELECT mydomain.listingItem.id, mydomain.listingItem.userId, mydomain.listingItem.createdAt, mydomain.listingItem.updatedAt, mydomain.listingItem.title, mydomain.listingItem.itemData 
FROM mydomain.listingItem 
WHERE JSON_EXTRACT(JSON_UNQUOTE(mydomain.listingItem.itemData), "$.instrument.description") LIKE "%organ%" 
AND JSON_TYPE(JSON_EXTRACT(JSON_UNQUOTE(mydomain.listingItem.itemData), "$.instrument.description")) = "STRING";

You'll see that the JSON must be unquoted before other JSON functions operate on the JSON in the given path.

There is also a matter of case insensitive matching - the MySQL default behavior is case sensitive - but this I feel lies outside of the scope of this reported issue.

[Aloysius999]

Any additional comments on this issue?
Looks like a bug to me.
Thanks