postgresql - SqlAlchemy: Querying the length json field having an array -

i have table field of type json. json field contains json object array. how query length of array each row?

class post(base):     __tablename__ = 'posts'     id = column(integer, primary_key=true)     items = column(json) 

example rows in table:

id: 1, items: [2,3,5] id: 2, items: [2,3,5, 8, 12] id: 3, items: [2,5] id: 4, items: [] 

expected result:

1 3 2 5 3 2 4 0 

is there this:

session.query(post.id, len(post.items)) 

note: json field contains array of complex objects, have used integers here purpose of illustration.

found solution

create class extends functionelement this

from sqlalchemy.sql.expression import functionelement sqlalchemy.ext.compiler import compiles class json_array_length(functionelement):     name = 'json_array_len'  @compiles(json_array_length) def compile(element, compiler, **kw):     return "json_array_length(%s)" % compiler.process(element.clauses) 

and use

session.query(post.id, json_array_length(post.items)) 

note: work on postgres because function 'json_array_length' defined in it. if want different db function need change. refer http://docs.sqlalchemy.org/en/rel_0_9/core/compiler.html#further-examples