Django numeric comparison of hstore or json data?
<p>Is it possible to filter a queryset by casting an hstore value to <code>int</code> or <code>float</code>?</p>

<p>I've run into an issue where we need to add more robust queries to an existing data model.  The data model uses the <code>HStoreField</code> to store the majority of the building data, and we need to be able to query/filter against them, and some of the values need to be treated as numeric values.</p>

<p>However, since the values are treated as strings, they're compared character by character and results in incorrect queries. For example, <code>'700' &gt; '1000'</code>.</p>

<p>So if I want to query for all items with a sqft value between 700 and 1000, I get back zero results, even though I can plainly see there are hundreds of items with values within that range.  If I just query for items with sqft value >= 700, I only get results where the sqft value starts with 7, 8 or 9.</p>

<p>I also tried testing this using a JsonField from <code>django-pgjson</code> (since we're not yet on Django 1.9), but it appears to have the same issue.</p>

<p><strong>Setup</strong></p>

<pre><code>Django==1.8.9
django-pgjson==0.3.1 (for jsonfield functionality)
Postgres==9.4.7
</code></pre>

<p><strong>models.py</strong></p>

<pre class="lang-python prettyprint-override"><code>from django.contrib.postgres.fields import HStoreField
from 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)
</code></pre>

<p><strong>Example Data</strong></p>

<p>This is an example of what some of the data on the hstore field looks like.</p>

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

<p><strong>Example Query which returns incorrect results</strong></p>

<pre><code>queryset = Building.objects.filter(data__total_sqft__gte=700)
</code></pre>

<p>I've tried playing around with the <code>annotate</code> feature to see if I can coerce it to cast to a numeric value but I have not had any luck getting that to work.  I always get an error saying the field I'm querying against does not exist. This is an example I found elsewhere which doesn't seem to work.</p>

<pre><code>queryset = Building.objects.all().annotate(
    sqft=RawSQL("((data-&gt;&gt;total_sqft)::numeric)")
).filter(sqft__gte=700)
</code></pre>

<p>Which results in this error:</p>

<blockquote>
  <p>FieldError: Cannot resolve keyword 'sqft' into field. Choices are: address1, address2, city, state, zipcode, data</p>
</blockquote>

<p>One thing that complicates this setup a little further is that we're building the queries dynamically and using <code>Q()</code> objects to and/or them together.</p>

<p>So, trying to do something sort of like this, given a key, value and operator type (<code>gte</code>, <code>lte</code>, <code>iexact</code>):</p>

<pre><code>queryset.annotate(**{key: RawSQL("((%data-&gt;&gt;%s)::numeric)", (key,)})
queries.append(Q(**{'{}__{}'.format(key, operator): value})
queries.filter(reduce(operator.and_, queries)
</code></pre>

<p>However, I'd be happy even just getting the first query working without dynamically building them out.</p>

<p>I've thought about the possibility of having to create a separate model for the building data with the fields explicitly defined, however there are over 600 key value pairs in the <code>data</code> hstore.  It seems like changing that into a concrete data model would be a nightmare to setup and potentially maintain.</p>