Sqlalchemy Filter Nested Jsonb Within Arrays
I have a Postgres JSONB field, with some nested arrays and other objects. from sqlalchemy.dialects.postgresql import JSONB class Family(db.Model): meta = db.Column(JSONB) jo
Solution 1:
The trick is to unnest the array or arrays using jsonb_array_elements()
, and then filter:
meta_value = literal_column('meta.value', type_=JSONB)
children_value = literal_column('children.value', type_=JSONB)
Family.query.\
with_entities(children_value['name'].astext).\
select_from(
Family,
func.jsonb_array_elements(Family.meta).alias('meta'),
func.jsonb_array_elements(
meta_value['children']).alias('children')).\
filter(children_value['name'].astext.contains('a'))
Note the use of literal_column()
to reference the values of the set returning function jsonb_array_elements()
.
Another option is to use jsonb_path_query()
(introduced in version 12):
name =column('name', type_=JSONB)
Family.query.\
with_entities(name.astext).\
select_from(
func.jsonb_path_query(
Family.meta,
'$[*].children[*].name').alias('name')).\
filter(name.astext.contains('a')).\
all()
Post a Comment for "Sqlalchemy Filter Nested Jsonb Within Arrays"