我有一个
JSONB字段,有时有嵌套键.例:
{“nested_field”:{“另一个URL”:“foo”,“一个简单的文本”:“text”},“first_Metadata”:“plain string”,“another_Metadata”:“foobar”}
如果我做
.filter(TestMetadata.Metadata_item.has_key(nested_field))
我得到了这个记录.
使用sqlAlchemy,以下内容适用于您的测试字符串:
class TestMetadata(Base): id = Column(Integer,primary_key=True) name = Column(String) Metadata_item = Column(JSONB)
按照SQLAlchemy documentation of JSONB
(搜索Path索引操作示例):
expr = TestMetadata.Metadata_item[("nested_field","a simple text")] q = (session.query(TestMetadata.id,expr.label("deep_value")) .filter(expr != None) .all())
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}'}