How to query on JSON field type with an object that contains at least the properties stored in the database

[mhmdsalahsebai]

Question

I have a Prisma model defining events stored in a PostgreSQL database. One of the fields is additionalFields, which is a JSON object. How can I construct a Prisma query to retrieve events, matching even if the additionalFields contain additional properties beyond those specified in the query? Here's my model:

model Event {
  id               Int            @id @default(autoincrement())
  eventType        String
  additionalFields Json
}

Suppose I have an event in the database with the following data:

{
id: 1,
eventType: "type1",
additionalFields: {
  "name": "This is a name",
  "amount": 123
} 

I want to construct a Prisma query that can match this event even if the additionalFields contain extra properties. For instance, the query below should match the event above:

const dataToQuery {
      eventType: "type1",
      additionalFields: {
       "name": "This is a name",
       "amount": 123,
       "foo": "bar",
       "whatever": "value"
      }
}

but this should not match,since there is a missing property:

const dataToQuery {
      eventType: "type1",
      additionalFields: {
       "name": "This is a name",
      }
}

How to reproduce (optional)

Expected behavior (optional)

No response

Information about Prisma Schema, Client Queries and Environment (optional)

// Add your schema.prisma

// Add any relevant Prisma Client queries here

• OS:

• Database:

• Node.js version:

• Run prisma -v to see your Prisma version and paste it

[ludralph]

Hi @mhmdsalahsebai 👋

Prisma Client does not support partial matching or ignoring additional properties in the JSON field. The JSON field in the query must exactly match the JSON field in the database.

For Example:

const getEvents = await prisma.event.findMany({  
 where: {  
  additionalFields: {  
   path: ['name'],  
   equals: 'This is a name',  
  },  
 },  
})

The query returns all events where the name property in additionalFields is This is a name.
However, this does not meet your requirement of matching events even if the additionalFields contain extra properties. Prisma Client does not currently support this feature.

However, you can open a feature request and document your usecase so our engineers can take a look.

[mhmdsalahsebai]

Hi @ludralph,

Thanks for your response.

Do you know if this is possible with raw SQL? I searched a lot and I didn't find anything thanks.