directus Add support for filtering in saved JSON values (objects & arrays)

Add support for filtering in saved JSON values (objects & arrays)

Open madc opened this issue 1 year ago • 8 comments

Describe the Bug

Up until version 9.14, we were able to use the _contains filter on a JSON field (multi-select dropdown) containing an array. Since updating to 9.15 the following error is shown, _contains is still suggested as possible filter.

We use this to fetch news for one or more categories.

To Reproduce

Create a Multi-Select dropdown field and try fetch records filtered by by a certain value:

query ($limit: Int = 10) {
  news(filter: { categories: { _contains: "media"}}, limit: $limit) {
    title
    slug
    copy
  }
}

Errors Shown

"json" field type does not contain the "_contains" filter operator

What version of Directus are you using?

9.15.1

What version of Node.js are you using?

16.16.0

What database are you using?

MySQL

How are you deploying Directus?

via npm

Aug 05 '22 22:08 madc

The _contains operator does not currently support all DB vendors as there some vendors may require the column to be casted as text type.

Related discussion on json filtering: #7277

Aug 08 '22 03:08 licitdev

I just just downgraded to Directus 9.14.5, no changes to code or database where made. The query works again, which means this functionality has been removed with 9.15.. Looking at the release notes, my best guess is, something happened in #14581.

Aug 08 '22 08:08 madc

@madc This is resulting from #14829 where the filter is validated against the following allowable filter operators.

https://github.com/directus/directus/blob/bbe7cb96a49e506140fcfe7fb00e87cf0aec182f/packages/shared/src/utils/get-filter-operators-for-type.ts#L44-L45

Aug 08 '22 08:08 licitdev

It is also essential for us to have the _contains filter for JSON fields. I was forced to downgrade to version 9.14 I hope it will be restored in the future otherwise I don't know what to do. Also because currently there is no other way to filter this type of field.

Aug 08 '22 08:08 nazariydj

@madc This is resulting from #14829 where the filter is validated against the following allowable filter operators.

https://github.com/directus/directus/blob/bbe7cb96a49e506140fcfe7fb00e87cf0aec182f/packages/shared/src/utils/get-filter-operators-for-type.ts#L44-L45

Thanks for pointing this out. After looking at this, there are currently two possible ways of dealing with this while staying on 9.15

Changing the field type of the multi-select dropdown to csv (an manually convert all content)
Create a custom endpoint as suggested in #7277

Both seem not ideal, also given that the default type for a multi-select dropdown is JSON. Is there a possibility to re-enable certain text filters (like _contains) for JSON again, until a better solution can be found?

I think it wouln't hurt anyone and at least help @nazariydj and me out..

Aug 08 '22 08:08 madc

Contains was never built to work reliably on json fields.. Contains does nothing more than a "contains substring" on a string value. In your use case, I think it worked 'on accident' as MySQL will run a LIKE query against JSON as a the stringified representation of the object. This behaves different in different DBs (f.e. PG saves it as binary, so it will flat out 500), and isn't too reliable because it doesn't behave they way you'd expect, for example: _ncontains "example" will fail on ["test", "example-fun"], as example if found as a substring in the value.

I don't think officially enabling "contains" for use on JSON fields is the right way to go here @licitdev, as that sets the wrong expectations. The real solution here is to implement the proper JSON search/extraction capabilities as per https://github.com/directus/directus/discussions/7277. Lets leave this issue open as the action item for #7277, as the question comes up often 👍🏻