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.


Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more