python - Django numeric comparison of hstore or json data? -

is possible filter queryset casting hstore value int or float?

i've run issue need add more robust queries existing data model. data model uses hstorefield store majority of building data, , need able query/filter against them, , of values need treated numeric values.

however, since values treated strings, they're compared character character , results in incorrect queries. example, '700' > '1000'.

so if want query items sqft value between 700 , 1000, 0 results, though can plainly see there hundreds of items values within range. if query items sqft value >= 700, results sqft value starts 7, 8 or 9.

i tried testing using jsonfield django-pgjson (since we're not yet on django 1.9), appears have same issue.

setup

django==1.8.9 django-pgjson==0.3.1 (for jsonfield functionality) postgres==9.4.7 

models.py

from django.contrib.postgres.fields import hstorefield django.db import models  class building (models.model):     address1 = models.charfield(max_length=50)     address2 = models.charfield(max_length=20, default='', blank=true)     city = models.charfield(max_length=50)     state = models.charfield(max_length=2)     zipcode = models.charfield(max_length=10)     data = hstorefield(blank=true, null=true) 

example data

this example of of data on hstore field looks like.

address1: ... address2: ... city: ... state: ... zipcode: ... data: {     'year_built': '1995',     'building_type': 'residential',     'building_subtype': 'single-family',     'bedrooms': '2',     'bathrooms': '1',     'total_sqft': '958', } 

example query returns incorrect results

queryset = building.objects.filter(data__total_sqft__gte=700) 

i've tried playing around annotate feature see if can coerce cast numeric value have not had luck getting work. error saying field i'm querying against not exist. example found elsewhere doesn't seem work.

queryset = building.objects.all().annotate(     sqft=rawsql("((data->>total_sqft)::numeric)") ).filter(sqft__gte=700) 

which results in error:

fielderror: cannot resolve keyword 'sqft' field. choices are: address1, address2, city, state, zipcode, data

one thing complicates setup little further we're building queries dynamically , using q() objects and/or them together.

so, trying sort of this, given key, value , operator type (gte, lte, iexact):

queryset.annotate(**{key: rawsql("((%data->>%s)::numeric)", (key,)}) queries.append(q(**{'{}__{}'.format(key, operator): value}) queries.filter(reduce(operator.and_, queries) 

however, i'd happy getting first query working without dynamically building them out.

i've thought possibility of having create separate model building data fields explicitly defined, there on 600 key value pairs in data hstore. seems changing concrete data model nightmare setup , potentially maintain.