postgres nested jsonb query I have a jsonb structure on postgres named data where each row (there are around 3 million of them) looks like this (I have GIN index on it):

[
    {
        "number": 100,
        "key": "this-is-your-key",
        "listr": "20 Purple block, THE-CITY, Columbia",
        "realcode": "LA40",
        "ainfo": {
            "city": "THE-CITY",
            "county": "Columbia",
            "street": "20 Purple block",
            "var_1": ""
        },
        "booleanval": true,
        "min_address": "20 Purple block, THE-CITY, Columbia LA40"
    },
    .....
]


I would like to query the min_address field in the fastest possible way. In Django I tried to use:

APModel.objects.filter(data__0__min_address__icontains=search_term)


but this takes ages to complete (also, "THE-CITY" is in uppercase, so, I have to use icontains here. This also has the problem that it ONLY searches the first elements - I'd want to search all the elements.

I tried dropping to rawsql like so:

cursor.execute("""\
    SELECT * FROM "apmodel_ap_model" 
    WHERE ("apmodel_ap_model"."data" 
    #&gt;&gt; array['0', 'min_address'])
    @&gt; %s \
    """,\
    [json.dumps([{'min_address': search_term}])]
)


but this throws me strange errors like:

LINE 4:       @&gt; '[{"min_address": "some lane"}]'       
              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.


I am wondering what is the fastest way I can query the field min_address by using rawsql cursors.
You don't want #&gt;&gt;, but instead #&gt;, from the docs


  
  #&gt;   Get JSON object at specified path
  #&gt;&gt;  Get JSON object at specified path as text
  


Also, you should consider normalizing that. Storing json-arrays, of json-objects and expecting a query on those objects to be fast is insane.