Support case insensitive advanced Json filtering

[Pehesi97]

Bug description

There is no support for case insensitive advanced JSON filtering on Prisma v2.23.0.

How to reproduce

Run any query filtering JSON fields and see that "string_contains" is case sensitive.

{
  applicantData :{
    path: ["details", "lastName"],
    string_contains: searchString,
  }
}

Expected behavior

There should be a field mode for this field type too (currently it's possible to filter other field types with case insensitivity)

Prisma information

It shouldn't be necessary to share Prisma information for this case.

Environment & setup

• OS: Mac OSX, Apple Silicon
• Database: PostgreSQL
• Node.js version: Node v15.14.0

Prisma Version

prisma               : 2.23.0
@prisma/client       : 2.23.0
Current platform     : darwin
Query Engine         : query-engine adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : adf5e8cba3daf12d456d911d72b6e9418681b28b
Studio               : 0.393.0
Preview Features     : filterJson

[palashCItobuz]

Is there a way to do this yet?

[turakvlad]

We need this feature as well. Is there any workaround for now?

[endo64]

We need this feature as well. Any plan to add this feature?

[redtailryan]

We need this as well, anything in the near future planned?

[VitoMedlej]

How is this feature not implemented yet? Its crucial in some applications.

[flesler]

This feature would be very neat to have. For string_contains and the other string ones. The array ones too ideally

[jeffdrumgod]

for MySQL, following this...: https://stackoverflow.com/a/59000485/694133 I tested it manually and it worked successfully... I will research how to implement it and try to submit a PR. But if someone can get it sooner that would be great :)

[shokhboz-abdullaev]

Any updates on this feature??

[pagreczner]

Hi, I would like to request this feature as well or understand a workaround if anyone has one.

[tbell511]

Would love this feature as well.

[ajhollowayvrm]

Bump on this

[rikardkling]

I would love this feature as well!

[vlapo]

Totally waiting for this feature too!

[multipliedtwice]

It would be great to have indeed.

[kindlyfire]

It's quite surprising seeing something like this left out. As bad as "bumps" are, I don't see a single contributor-made comment here and the issue has existed for almost two years.

[janpio]

I am a maintainer here. We are aware this issue exists and we added labels to it to reflect that we understood it. When we have capacity and this gets enough priority among all our issues, we might implement it in the future. No more bumps necessary unless they add new information or additional use cases. Thank you.

[FacundoSpira]

Is there any workaround?

[raaauf6933]

Hello, Does anyone have work around on this one?

[muvaf]

No more bumps necessary unless they add new information or additional use cases.

The use case we have is that we're storing email addresses as JSON so that they can include name, i.e. {"name": "Foo Bar", "address": "[email protected]"}. For inbound emails, we need to match with a contact for association and since email RFC states that addresses are case-insensitive, we're not able to get a match using Prisma when the address has characters with different casing.

Looks like we'll end up converting all addresses to lowercase before saving to database but that does mean we're making a change to the user data which we'd have preferred not to do even if it's essentially the same from definition point of view.

[baladao]

help us please

[spencer-robertson]

We also have a use case, though maybe theres a better option out there for us? We have some data stored in JSON that a user can search for. e.g.

{
    name: "John Doe",
    position: "Software Engineer"
}

Our users may want to search for "engineer", but nothing would show up without specifically searching for "Engineer".

In the mean time our work around is to try search for the original value, the lower case version, the upper case version and the capitalised version. This isn't perfect but hits most of our searches pretty well

{
	OR: [
		{
			data: {
				path: [filter],
				string_contains: value,
			},
		},
		{
			data: {
				path: [filter],
				string_contains: value.toLowerCase(),
			},
		},
		{
			data: {
				path: [filter],
				string_contains: value.toUpperCase(),
			},
		},
		{
			data: {
				path: [filter],
				string_contains:
					value.toLowerCase().charAt(0).toUpperCase() +
					value.slice(1),
			},
		},
	],
}

If theres something else we could do for our use case please let me know

[LizDodion]

i'd also love this. searching on an open body in a json field property is tricky and needs to be case insensitive

[mwibutsa-koin]

Need this ASAP, any updates or work around ?

[multipliedtwice]

Need this ASAP, any updates or work around ?

Have you tried to keep lowercased copy in another column and match them by id?

[dmitriyzhuk]

Need this ASAP, any updates or work around ?

Have you tried to keep lowercased copy in another column and match them by id?

Yeah, that's what I did. but it's still an ugly solution. but it works for the time being.

[johnMorone]

We also have a use case, though maybe theres a better option out there for us? We have some data stored in JSON that a user can search for. e.g.

Thank you @spencer-robertson ! This was very helpful to us, as this covers the vast majority of our cases in the mean time without requiring data-side hacks. It seems there is a small cost in performance, but it seems worth it.

If anyone else would find this useful, I created a utility to make this method easier to use as needed

const JsonMultiCaseQuery = (path: string[], string_contains: string, jsonProperty: string): {OR: Prisma.Enumerable<{[x: string]: Prisma.JsonFilter}>} => ({
  OR: [
    // as TypEd
		{
			[jsonProperty]: {
				path,
				string_contains,
			},
		},
    // lower case
		{
			[jsonProperty]: {
				path,
				string_contains: string_contains.toLowerCase(),
			},
		},
    // UPPER CASE
		{
			[jsonProperty]: {
				path,
				string_contains: string_contains.toUpperCase(),
			},
		},
    // Capitalized
		{
			[jsonProperty]: {
				path,
				string_contains:
					string_contains.charAt(0).toUpperCase() +
					string_contains.toLowerCase().slice(1),
			},
		},
	],
});

If anyone has suggestions on better typing, I'd welcome feedback. Prisma is new to me and I wanted to avoid typing this specifically for a given model or schema with generated types. I tried generic jsonProperty: T and keying with a mapped type [x in T], but ran into some issues, so went with a string for simplicity for now.

[sefaun]

we need this feature. please do it :)

[birosrichard]

Bump

[hengkydev]

+1 forced to use raw query 😔

[Aloysius999]

This issue is over 3 years old.
I have tested JSON search and it's still case sensitive (Prisma v5.17.0)
Any news on when this issue might get fixed?

The fix should just add LOWER in the JSON comparison

((JSON_UNQUOTE(LOWER(JSON_EXTRACT(db.table.jsoncol, "$.path"))) LIKE LOWER("%term%")

My (rather ugly) work-around is a separate JSON column all lowercase just for search.