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

[madc]

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

[licitdev]

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

[madc]

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.

[licitdev]

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

directus/packages/shared/src/utils/get-filter-operators-for-type.ts

Lines 44 to 45 in bbe7cb9

	case 'json':
	return ['null', 'nnull'];

[nazariydj]

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.

[madc]

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

directus/packages/shared/src/utils/get-filter-operators-for-type.ts

Lines 44 to 45 in bbe7cb9

	case 'json':
	return ['null', 'nnull'];

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 Allow filtering in stored JSON objects #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..

[rijkvanzanten]

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 #7277. Lets leave this issue open as the action item for #7277, as the question comes up often 👍🏻

[petitroto]

This issue appears to be resolved by #15889

[infomiho]

Will the mentioned PR enable using contains in the UI as well?

[rijkvanzanten]

Linear: ENG-213

[YuryYCasumo]

Does it mean that contains won't work on UI also? Given that Enabled Countries field is M2M.