Efficient way to partially match stringified JSON in a string column using Prisma (without full JSON match)

Question

I'm trying to find a record where a column (payload) stores a stringified JSON object. The column type is string (as defined in Prisma schema) — so it's not a JSON or JSONB type.

I want to check if a subset of the JSON content exists in the string, without matching the entire object.

Here's a sample value stored in the payload column:

{
  "triggerEvent": "RESERVATION_EXPIRED",
  "id": 16,
  "eventTypeId": 3,
  "userId": 4,
  "slotUtcStartDate": "2025-07-25T03:30:00.000Z",
  "slotUtcEndDate": "2025-07-25T04:00:00.000Z",
  "uid": "014cbb69-fa4b-421b-8c6d-af0ac7f4184e",
  "releaseAt": "2025-07-24T20:56:33.000Z",
  "isSeat": false
}

I'm only interested in this part of the object:

"eventTypeId": 3,
"userId": 4,
"slotUtcStartDate": "2025-07-25T03:30:00.000Z",
"slotUtcEndDate": "2025-07-25T04:00:00.000Z",
"uid": "014cbb69-fa4b-421b-8c6d-af0ac7f4184e"

Here’s my current Prisma-based query:

const { id, releaseAt, isSeat, ...rest } = slot;
const restString = JSON.stringify(rest);
const searchString = restString.slice(1, -1); // remove outer {}

const isWebhookScheduledTriggerExists = await prisma.webhookScheduledTriggers.findFirst({
  where: {
    payload: {
      contains: searchString,
    },
  },
});

The Problem:

I was told this approach is inefficient and not production-safe.

Constraint:

I cannot convert the column to JSON/JSONB or change its type — it must remain a string.

My Question:

Is there a more efficient or reliable way to match a partial stringified JSON object in a string column using Prisma or raw SQL?

I was told this approach is inefficient and not production-safe...was a specific reason given for this? If someone is going to give you this kind of feedback they need to provide an explanation, so that you have some idea of what kind of things you might need to change. Get some proper feedback so you've got a clear starting point. — ADyson
– ADyson, Commented Jul 25 at 9:00
I cannot convert the column to JSON/JSONB or change its type — it must remain a string this requirement means there's not much else you can do. — Rory McCrossan
– Rory McCrossan, Commented Jul 25 at 9:01
Your current implementation requires that the stored JSON string have all the same keys in the same order as as your search string, which is somewhat reasonable but also brittle. You could break it down into key by key searches which will be less efficient but more robust. — pilchard
– pilchard, Commented Jul 25 at 9:55
The explanation was, it would be very slow in production evironment — Giri patel
– Giri patel, Commented Jul 25 at 10:08
"I was told this approach is inefficient and not production-safe." What does "not production-safe" mean here? Why do you store all values in one string instead of separate columns? I agree that the current implementation is bad, but I don't agree with your approach. Your goal is a full table scan for a substring. That's horrible. In addition, you're wasting so much space for the format of the JSON. Why do you store the newlines and spaces? Store a minified version. — user16540390
– user16540390, Commented Jul 25 at 10:17

Stefanov.sm · Accepted Answer · 2025-07-25 16:03:27Z

0

I would do it in native SQL using the <@ operator:

select 
('{'||
'"eventTypeId": 3,
"userId": 4,
"slotUtcStartDate": "2025-07-25T03:30:00.000Z",
"slotUtcEndDate": "2025-07-25T04:00:00.000Z",
"uid": "014cbb69-fa4b-421b-8c6d-af0ac7f4184e"'
|| '}')::jsonb <@
'{
  "triggerEvent": "RESERVATION_EXPIRED",
  "id": 16,
  "eventTypeId": 3,
  "userId": 4,
  "slotUtcStartDate": "2025-07-25T03:30:00.000Z",
  "slotUtcEndDate": "2025-07-25T04:00:00.000Z",
  "uid": "014cbb69-fa4b-421b-8c6d-af0ac7f4184e",
  "releaseAt": "2025-07-24T20:56:33.000Z",
  "isSeat": false
}'::jsonb

As a parameterized query this would be as simple as

select ('{'|| ? || '}')::jsonb <@ payload::jsonb
from the_table where ....

edited Jul 25 at 16:03

answered Jul 25 at 13:42

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Efficient way to partially match stringified JSON in a string column using Prisma (without full JSON match)

The Problem:

Constraint:

My Question:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The Problem:

Constraint:

My Question:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related