1

I'm trying to find the best way to generate a sql query that gets me a specific field from a jsonb column from my db. I want to generate a query like this:

select images->'main' from scenes where id = 1;

I found this reference for using with_entities, and this generates the query correctly:

>>> x = Scene.query.with_entities(Scene.images['main']).first()                                                                                                                                                                        
2016-09-22 18:57:01,825 INFO sqlalchemy.engine.base.Engine SELECT scenes.images -> %(images_1)s AS anon_1                                                                                                                              
FROM scenes                                                                                                                                                                                                                            
 LIMIT %(param_1)s                                                                                                                                                                                                                     
2016-09-22 18:57:01,826 INFO sqlalchemy.engine.base.Engine {'param_1': 1, 'images_1': 'main'}

However, it's returning the result as a tuple rather than a model object:

>>> x                                                                                                                                                                                                                                  
([{u'url': u'foo.jpg', u'priority': 1}],)

I considered using load_only, but this API doesn't seem to have a way of specifying jsonb fields of a column, but only an entire column name.

Is there a way to generate the query and have the model object as the return value?

Community
  • 1
  • 1
zoopz
  • 1,099
  • 2
  • 9
  • 10

2 Answers2

0

Assuming that Scene is an ORM class, you can use Session.query() to either return the value as a tuple

x = session.query(Scene.images['main']).filter_by(id=1).one()
print x

SELECT images->'main' FROM scenes WHERE id = 1;

or the model object (instance of Scene)

scene = session.query(Scene).filter_by(id=1).one()
print scene.images['main']

SELECT * FROM scenes WHERE id = 1;
mdh
  • 5,355
  • 5
  • 26
  • 33
0
x = db.session.query(Scene.images['main'].label('main_image')).first()
print(x.main_image)
arstj
  • 88
  • 1
  • 1
  • 7