order_by orders Json numbers like strings

[jamesstidard]

It appears Pony is stringifying the values in json before it it orders so numbers are sorted alphabetically. Is this a known limitation?

Here's an example script:

from pony.orm import (
    Database, Required, Optional, db_session, commit,
    show, Json)

db = Database()
db.bind('sqlite', filename=':memory:', create_db=True)

class Person(db.Entity):
    name = Required(str)
    body = Optional(Json, default={})

db.generate_mapping(create_tables=True)


with db_session:
    Person(name='bort', body={'age': 1})
    Person(name='baz', body={'age': 2})
    Person(name='bar', body={'age': 10})
    Person(name='foo', body={'age': 20})

    commit()

    people = Person.select().order_by(lambda p: p.body['age'])
    show(people)

id|name|body       
--+----+-----------
1 |bort|{'age': 1} 
3 |bar |{'age': 10}
2 |baz |{'age': 2} 
4 |foo |{'age': 20}

[kozlovsky]

Should work now in all databases except Oracle. In Oracle you need to explicitly cast value to int:

people = Person.select().order_by(lambda p: int(p.body['age']))

This is also necessary in other databases if numbers are stored in JSON as a string values

[jamesstidard]

@kozlovsky Second issue of mine you've taken on - much appreciated, thanks. I did try casting to an int before as a work around but I was getting the exception "TypeError: Function 'int' cannot be used this way: int(p.body['age'])". That might have been sqlite specific and not an issue anymore though.

Thanks again.