Unable to Perform Fuzzy Search on JSON Field with icontains Filter

[sleep1223]

Describe the bug
@henadzit Hello bro, I am trying to perform a fuzzy search on a JSON field (json_field) using the icontains filter in Tortoise ORM. However, I am encountering an error when attempting to filter the JSON array.

To Reproduce

await Goods.create(json_field=[{"goodsId": 111, "goodsName": "111"}, {"goodsId": 222, "goodsName": "222"}])

result = await Goods.filter(json_field__filter=[{"goodsName__icontains": "11"}])

result = await Goods.filter(json_field__filter={"0__goodsName__icontains": "11"})  # it work, but can't determine the index

Expected behavior

Traceback (most recent call last):
  File "D:\Project\pxwl\fast-soy-zhaoshan\app\scripts\testxxxxx.py", line 25, in <module>
    asyncio.run(main())
  File "D:\Scoop\apps\python310\3.10.11\lib\asyncio\runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "D:\Scoop\apps\python310\3.10.11\lib\asyncio\base_events.py", line 649, in run_until_complete
    return future.result()
  File "D:\Project\pxwl\fast-soy-zhaoshan\app\scripts\testxxxxx.py", line 20, in main
    result = await OfflineExpense.filter(commission_value__filter=[{"goodsName__icontains": '11'}])
  File "D:\Project\pxwl\fast-soy-zhaoshan\.venv\lib\site-packages\tortoise\queryset.py", line 1133, in __await__
    self._make_query()
  File "D:\Project\pxwl\fast-soy-zhaoshan\.venv\lib\site-packages\tortoise\queryset.py", line 1100, in _make_query
    self.resolve_filters()
  File "D:\Project\pxwl\fast-soy-zhaoshan\.venv\lib\site-packages\tortoise\queryset.py", line 141, in resolve_filters
    modifier &= node.resolve(
  File "D:\Project\pxwl\fast-soy-zhaoshan\.venv\lib\site-packages\tortoise\expressions.py", line 478, in resolve
    return self._resolve_kwargs(resolve_context)
  File "D:\Project\pxwl\fast-soy-zhaoshan\.venv\lib\site-packages\tortoise\expressions.py", line 441, in _resolve_kwargs
    filter_modifier = self._resolve_regular_kwarg(
  File "D:\Project\pxwl\fast-soy-zhaoshan\.venv\lib\site-packages\tortoise\expressions.py", line 395, in _resolve_regular_kwarg
    criterion, join = self._process_filter_kwarg(resolve_context.model, key, value, table)
  File "D:\Project\pxwl\fast-soy-zhaoshan\.venv\lib\site-packages\tortoise\expressions.py", line 383, in _process_filter_kwarg
    criterion = op(table[param["source_field"]], encoded_value)
  File "D:\Project\pxwl\fast-soy-zhaoshan\.venv\lib\site-packages\tortoise\contrib\postgres\json_functions.py", line 148, in postgres_json_filter
    key_parts, filter_value, operator_ = get_json_filter_operator(value, operator_keywords)
  File "D:\Project\pxwl\fast-soy-zhaoshan\.venv\lib\site-packages\tortoise\filters.py", line 369, in get_json_filter_operator
    ((key, filter_value),) = value.items()
AttributeError: 'list' object has no attribute 'items'

Additional context
Add any other context about the problem here.

[henadzit]

@sleep1223 I'm afraid you cannot use icontains in this way. There are a few alternatives:

1. Use RawSQL. Below is one of the ways to do it.

 await Goods.annotate(goods_name_match=RawSQL(
        "(SELECT 1 FROM jsonb_array_elements(json_field) AS elem WHERE elem->>'goodsName' ILIKE '111')"
    )).filter(goods_name_match=1)

But be aware that this option is vulnerable to SQL injections.

2. If case-insensitive search is not a requirement, you can do the following:

await Goods.filter(json_field__contains=[{"goodsName": "111"}])