Django - Postgresql - Поле JSON - оператор #>> - индекс вне диапазона

В проекте django, использующем БД postgresql, есть коллекция 'table1', которая имеет JSON-поле под названием 'data'. В этом поле JSON мы будем хранить email по динамическому ключу. Например:

ID | DATA
1  | '{"0_email": "user1@mail.com"}'
2  | '{"3_email": "user2@mail.com"}'
3  | '{"1_email": "user3@mail.com"}'

Заявление о проблеме:

Отфильтруйте строки, в которых в поле "данные" существует "user2@mail.com".

Мой подход: 

from django.db import connection

@transaction.atomic
def run(given_email):
    with connection.cursor() as crsr:
        crsr.execute(
            """
            DECLARE mycursor CURSOR FOR
            SELECT id, data
            FROM table1
            WHERE
                data #>> '{}' like '%\"%s\"%'
            """,
            [given_email]
        )
        while True:
            crsr.execute("FETCH 10 FROM mycursor")
            chunk = crsr.fetchall()
            # DO SOME OPERATIONS...

Объяснение для data #>> '{}' like '%\"%s\"%':

  1. I am using the #>> operator to get object at specific path of JSON as text.
  2. I am providing '{}' empty path so that I will get the complete JSON as a text.
  3. From this stringified JSON, I am checking if the given_email (user2@mail.com from the above example) is present

Затем я направил эту функцию на API в django, в котором я получу given_email в полезной нагрузке. Я столкнулся со следующей ошибкой, которая вызывает эту функцию:

Traceback (последний последний вызов): Файл "project/lib/python3.9/site-packages/django_extensions/management/debug_cursor.py", строка 49, в execute return utils.CursorWrapper.execute(self, sql, params) Файл "project/lib/python3.9/site-packages/django/db/backends/utils.py", строка 67, in execute Файл "project/lib/python3.9/site-packages/django/db/backends/utils.py", строка 76, in _execute_with_wrappers Файл "project/lib/python3.9/site-packages/django/db/backends/utils.py", строка 87, в _execute self.db.validate_no_broken_transaction() IndexError: tuple index out of range

.

Во время обработки вышеуказанного исключения произошло другое исключение:

Traceback (последний последний вызов): File "", line 1, in Файл "project/lib/python3.9/site-packages/django_extensions/management/debug_cursor.py", строка 54, в execute raw_sql = raw_sql[:truncate] TypeError: 'NoneType' object is not subscriptable

.

Наблюдения:

  1. I don't think there is anything wrong with the query. In fact, I have tried this query in DBeaver and I am getting the expected response.
  2. I am suspecting that the '{}' part in the query is causing some issue. So, I have tried to replace it with '\\{\\}', '{{}}' but it didn't work.

Во-первых, '{}' интерпретируется как text, тогда как json-путь имеет тип text[]. Поэтому в запросе вы должны привести его к типу text[]: WHERE data #>> '{}' :: text[] like '%\"%s\"%'

Тогда data #>> '{}' :: text[] можно упростить до data :: text, что даст тот же результат.

Наконец, вы преобразуете данные json в текст, а затем используете оператор pattern matching like. Это решение может дать неожиданные результаты с некоторыми значениями, которые содержат ожидаемый email в качестве подстроки, но не равны этому ожидаемому email.

Для того, чтобы получить точный результат для ожидаемого email, необходимо использовать функцию jsonb, например :

WHERE jsonb_path_exists(data :: jsonb, ('$.* ? (@ == "' || expected_email || '")') :: jsonpath)
Вернуться на верх