Как искать подстроку в массиве полей JSON?

Я использую поле JSONField, предоставляемое Django, и храню в этом поле данные такого типа:

[
        {
            "number": 1,
            "text": "This text is about dogs"
        },
        {
            "number": 2,
            "text": "Only cats in this text here"
        },
        {
            "number": 3,
            "text": "However, this text does also contain dogs"
        },
    ]

Что я пытаюсь достичь, это своего рода подстрочное соответствие - то есть, если человек ищет строку "dog", результат должен возвращать что-то вроде:

        {
            "number": 1,
            "text": "This text is about dogs"
        },
        {
            "number": 3,
            "text": "However, this text does also contain dogs"
        },

Глядя на документацию Django, кажется, что можно запросить поля JSON, как например

Model.objects.filter(field__text__contains='dogs')

Однако, contains работает только для одиночных значений словаря, а не когда есть массив словарей.

Есть советы? Либо через Django ORM, либо через Postgres напрямую.

Я предположил, что у вас есть столбец ID в таблице, в которой вы ищете, и важно сохранить эту информацию в целости. То, что вы хотите, можно сделать только с помощью типа данных Postgres jsonb (по крайней мере, я не могу придумать другого способа), и вам, вероятно, следует пересмотреть способ хранения данных в базе данных, если вам нужно искать в массиве json-объектов без ключей верхнего уровня, чтобы облегчить поиск и ссылки в запросах.

Итак, вот мой вариант базового запроса postgres, который ищет совпадения в паре ключ-значение 'text', а затем удаляет несовпадающие значения.

WITH search_set AS (
        SELECT  t.id,
                t.js
        FROM ( VALUES (1,'[{"number": 1,"text": "This text is about dogs"},{"number": 2,"text": "Only cats in this text here"},
        {"number": 3,"text": "However, this text does also contain dogs"}]'::jsonb) 
        ) AS t(id,js) 
        WHERE jsonb_path_exists(js, '$.text[*] ? (@ like_regex "(dogs)" flag "i")') 
)
SELECT rs.id,
      jsonb_agg(arr.elem)
FROM search_set rs,
jsonb_array_elements(rs.js) AS arr(elem)
WHERE
    arr.elem::varchar ILIKE '%dog%'
    GROUP BY rs.id 
 ;
Вернуться на верх