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