3

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.

2
  • 1
    It's interesting to me that you cropped out the ERROR: part of the message. Apr 16, 2018 at 20:18
  • 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. Apr 17, 2018 at 7:54

1 Answer 1

3

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.

2
  • Odd that being lazy and performant at the same time is so difficult. Apr 17, 2018 at 7:45
  • @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. Apr 17, 2018 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.