JSON-specific filtering

[tomhoule]

Problem

When working with Json fields, some databases (like postgres) let you manipulate the JSON value. This is useful for filtering, for example if you want to filter a model with a Json field where one object field (or a specific path) inside the json column matches a provided value. There are other operations like "object contains", "path exists", etc. that would be useful.

Additional context

This issue is the prolongation of #2345 - which is about simple filtering by Json field by equality, treating it as a scalar.

[luxaritas]

Few notes:

• Django is shipping JSONField support to all DBs in 3.1, which includes full document querying - might be worthwhile taking a peek at what they've done
• If I understand correctly, you should be able to perform any typical filtering operation on a JSON value - ie in a where clause, you could verify myjson.some.value > 5
• At least in Postgres (though I assume in other engines), you can also index on individual keys

Would also just like to mention that as I'm evaluating Prisma as a possibility for our future backend rewrite, this is quite important for us. We have data that is not really viable to store relationally, but still needs to be queryable - and in some cases that dataset could be quite large. If it's helpful at any point, I'm happy to describe more about our use cases for this

[luxaritas]

Another thought - it might be nice to have built-in support, or at least a recommended approach, for validating a JSON schema when updating a JSON field

[JoniBR]

This feature would be really helpful! For example, we have a column integrations on our User table which holds a JSON with some external ids. A lot of times we need to filter the users by having a certain key inside that JSON.

[tre-dev]

Can this please get pushed on the priority list? 🙏

[tradical-allstripes]

+1

This feature would be immensely helpful for me and my team

[michael-land]

really want this feature

[kladnik]

This is really needed and also PostgreSQL supports this naturally with queries. Can we get this soon?

[dpetrick]

This is finished and released in 2.23, integration is done.

[matthewmueller]

JSON Filtering Documentation is also up: https://www.prisma.io/docs/concepts/components/prisma-client/working-with-fields/working-with-json-fields

If you run into anything, let us know in #7135!