Django - Postgresql - JSON Field - #>> operator - Index of out range

Question

In a django project which is using postgresql DB, there is a collection called 'table1' which has a JSON field called 'data'. In this JSON field, we will be storing an email against a dynamic key. For ex:

ID | DATA
1  | '{"0_email": "[email protected]"}'
2  | '{"3_email": "[email protected]"}'
3  | '{"1_email": "[email protected]"}'

Problem Statement:

Filter out the rows in which "[email protected]" exists in the "data" field.

My Approach:

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...

Explanation for 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 ([email protected] from the above example) is present

Then, I have pointed this function to a API in django in which I will get the given_email in payload. I am facing the below error which triggering this function:

Traceback (most recent call last): File "project/lib/python3.9/site-packages/django_extensions/management/debug_cursor.py", line 49, in execute return utils.CursorWrapper.execute(self, sql, params) File "project/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute File "project/lib/python3.9/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers File "project/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute self.db.validate_no_broken_transaction() IndexError: tuple index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "", line 1, in File "project/lib/python3.9/site-packages/django_extensions/management/debug_cursor.py", line 54, in execute raw_sql = raw_sql[:truncate] TypeError: 'NoneType' object is not subscriptable

Observations:

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.

Answer 1

First, '{}' is interpreted as a text whereas a json path is of type text[]. So you should cast it as text[] in your query : WHERE data #>> '{}' :: text[] like '%\"%s\"%'

Then data #>> '{}' :: text[] could be simplified as data :: text which will provide the same result.

Finally, you convert your json data into a text and then you use the pattern matching like operator. This solution may provide some unexpected results with some values which contains the expected email as a substring but which are not equal to that expected email.

In order to have the exact result for the expected email, you should use a jsonb function, for example :

WHERE jsonb_path_exists(data :: jsonb, ('$.* ? (@ == "' || expected_email || '")') :: jsonpath)