prisma
Support case insensitive advanced `Json` filtering
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
How is this feature not implemented yet? Its crucial in some applications.
This feature would be very neat to have. For string_contains and the other string ones. The array ones too ideally
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 :)
Hi, I would like to request this feature as well or understand a workaround if anyone has one.
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.
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.
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.
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
i'd also love this. searching on an open body in a json field property is tricky and needs to be case insensitive
Need this ASAP, any updates or work around ?
Have you tried to keep lowercased copy in another column and match them by id?
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.
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.
