Allow filtering in stored JSON objects

[rijkvanzanten]

Some databases, like Postgres and more recent versions of MySQL have (limited) capabilities to search in JSON objects, instead of treating them as a flat value. Postgres f.e. has a dedicated syntax for it, where MySQL has a "EXTRACT_JSON" function that can be used to extract values from the JSON object.

Seeing this would require a lot of custom magic per DB driver, I think this could be implemented in a similar fashion as the date extraction functions (year(datetime_created) etc):

json(my_json_field, path.to.the.value)

The second argument would be a dot-notation path of where to find the value in the JSON, similar to lodashes get function

[casualmatt]

Because now we can filter records by a json field with "is null" or "isn't null" and is a bit annoying and frustrating.

[rasco]

this feature would be great.

json functions are already in Knex, so it probably wouldn't require custom magic per DB driver: http://knexjs.org/#Builder-whereJsonObject

[tspvivek]

@rijkvanzanten Is there any workaround or hack to filter in JSON objects now?

[rijkvanzanten]

In a custom hook/endpoint, you can access the knex instance used to communicate with the database, at which point you could run some custom SQL to add filtering capabilities based on your specific database vendor.

[tspvivek]

Thanks. Will try this.

[rasco]

@tspvivek take a look at this. maybe you don't need SQL: http://knexjs.org/#Builder-whereJsonObject

[piotr-cz]

I believe new link is: http://knexjs.org/guide/schema-builder.html#json

[hermesalvesbr]

Any chance of this coming back to the API?

[hermesalvesbr]

[madc]

See here: #14894 (comment)

[hermesalvesbr]

See here: #14894 (comment)

manually convert all content or create a endpoint :( I saw that.

[rijkvanzanten]

@hermesalvesbr contains is the operator that will find a substring in a piece of text. It has never worked on JSON fields in Postgres (which is what I'm assuming you're using based on the error message), and was never designed for use with JSON fields in the first place 👍🏻

[hermesalvesbr]

I understood. But, from api, his another way to filter a json? Just search all collection?

[madc]

This issue just got a little bump via #14894.

[AaronBeaudoin]

Just want to resurface this discussion/issue because it's been since May 2022 that I discovered it also affect's built-in Directus tables as well as being a user limitation.

In short, the Tags field in the Directus file library is essentially useless. This is because the purpose of tags is generally understood to be to enable searching/filtering items based on whether a given tag is present, but since the tags field in the Directus file library is (presumably) a JSON object, we can't actually use it for searching/filtering. When you try filter on it you get the options "Is null" and "Isn't null", both of which make no sense in the context of tags.

I'd honestly argue that the Tags field should just be removed entirely until this issue is resolved, because right now what exactly would anyone use it for other than searching/filtering as described above? I think this issue is more of a problem than it initially appeared. Alternatively, the Tags field could be converted to an actual database relation, but I think the more helpful direction is to focus on resolving this issue and bring the benefits of JSON filtering to everyone.

[connorwinston]

JSON querying is in the works right now, check out the progress at #15889

[slysiou]

Great to see the support on Rest API. But, when do you plan to add it on UI ?
Thanks

[rijkvanzanten]

It'll come to the UI as well, but I don't have a timeline for it 👍🏻

[rijkvanzanten]

Heya! Thanks for opening this feature request!

This feature request has received over 15 votes from the community. This means we'll move this feature request to the Under Review state!

The Core team will schedule a meeting to review this request as soon as possible. The discussion will then be approved or denied. You may or may not be invited to join this meeting with the core team.

For more information, see our Feature Request Process.

[tspvivek]

Its already part of #15889, right?

[rijkvanzanten]

Yeah! Bear with me here while we cleanup all the open feature requests 😄 There's a handful current in under-review that are already (partially) done or otherwise in-flight 🙂

[w0kyj]

Data Abstraction Project dependent:

• use relational nested notation to reference the structure
• works for any data source
• how is understood --> add RFC to ensure details defined

[phazonoverload]

On January 11 we're inviting you to come and have a chat with us about this feature request at our Request Review in our Discord server. This will help us have some dedicated time to talk about what implementation would have the most sense. Please feel free to join us as https://directus.chat (the event is already listed and you can set a reminder)

[w0kyj]

Feature Request Session Notes
Filtering Qs:

• deep/nested filtering of a JSON path within the object
• filter/search arrays
• general text search of the "JSON" blob

Tim: I divided it up in 3 different functionalities in that old #15889

• fields extracting data from a json field
• filter using specific json fields and filter against that
  • filter for the fields(key), then filter on the values of those fields
• deep actually filter inside the json blob

Is the idea to integrate Directus's existing filters/aggregate/Dynamic Variables function or would there be new JSON specific functions?

• existing filtering for sure
• may include specific filter operators as needed same as other data types today

psql allows for dateranges (a JSON array) and I built a custom daterange interface/display for this but can't filter with it. Is that something that would be supported by this pr or would that be something else?(unsure of other DBs)

• Yes this would be supported by the new feature

Could you consider using JSON schema to type and validate fields.

• interesting idea, need to think through
  • schema for a specific field
  • schema for a collection
  • schema for a item
  • Need a way to define schema
• data validation/enforcement based on the defined schema
  • great for page builder use case -- use json field for page content and other table fields for routes, dates, status, etc
  • repeater interface
  • relational repeater interface
    • no guarantees that data is a key or value
    • Mongo does this with special "type" object id that is globally unique
    • performance will be a key consideration here (some vendors may support, while others won't)
    • This will be a separate feature discussion (it is very tricky)
• There is a Portable Text spec https://github.com/portabletext/portabletext

[jofmi]

Hi! Is there a timeline for this feature to also come to the UI? :)

[phazonoverload]

We don't offer timelines for any work that we do, sorry.

[wfpaisa]

greetings, I am also interested in this issue, I hope it is resolved soon, as I see that the issue has been going on for several years.