SQLAlchemy根据JSONB中的嵌套键进行过滤 [英] SQLAlchemy filter according to nested keys in JSONB

问题描述

我有一个JSONB字段，该字段有时具有嵌套键.示例:

I have a JSONB field that sometimes has nested keys. Example:

{"nested_field": {"another URL": "foo", "a simple text": "text"},
 "first_metadata": "plain string",
 "another_metadata": "foobar"}

如果我愿意

.filter(TestMetadata.metadata_item.has_key(nested_field))

我得到了这个记录.

如何搜索嵌套键的存在? ("a simple text")

How can I search for existence of the nested key? ("a simple text")

推荐答案

对于SQLAlchemy，以下应该适用于您的测试字符串:

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)

按照 (搜索路径索引操作示例):

expr = TestMetadata.metadata_item[("nested_field", "a simple text")]
q = (session.query(TestMetadata.id, expr.label("deep_value"))
     .filter(expr != None)
     .all())

应会在下面生成SQL

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}'}

这篇关于SQLAlchemy根据JSONB中的嵌套键进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！