postgres nested jsonb query

Question

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" 
 #>> array['0', 'min_address'])
 @> %s \
 """,\
 [json.dumps([{'min_address': search_term}])]
)

but this throws me strange errors like:

LINE 4: @> '[{"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.

• Evan Carroll: It's interesting to me that you cropped out the ERROR: part of the message.
• Gerard H. Pille: BTW. gin indices are used for full text searches. If you did a full text search first, and applied your filter to the result set, you might be able to reduce the number of ages it takes. If not, better drop that index.

Answer 1

You don't want #>>, but instead #>, from the docs

• #> Get JSON object at specified path
• #>> 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.

• Gerard H. Pille: Odd that being lazy and performant at the same time is so difficult.
• Evan Carroll: @GerardH.Pille not really odd at all, if you don't tell the database anything about your schema then you're also not telling it how to store the data, index the data, or ensure the integrity of the data. This is why document stores didn't replace RDBMS, they only compliment it. My bet is you'd be happier, and better off if you didn't use them at all, then if you used them heavily.