SQLAlchemy filter according to nested keys in JSONB
With SQLAlchemy the following should work for your test string:
class TestMetadata(Base):
id = Column(Integer, primary_key=True)
name = Column(String)
metadata_item = Column(JSONB)
as per SQLAlchemy documentation of JSONB
(search for Path index operations example):
expr = TestMetadata.metadata_item[("nested_field", "a simple text")]
q = (session.query(TestMetadata.id, expr.label("deep_value"))
.filter(expr != None)
.all())
which should generate the SQL
below:
SELECT testmetadata.id AS testmetadata_id,
testmetadata.metadata_item #> %(metadata_item_1)s AS deep_value
FROM testmetadata
WHERE (testmetadata.metadata_item #> %(metadata_item_1)s) IS NOT NULL
-- @params: {'metadata_item_1': u'{nested_field, a simple text}'}