Фильтр SQLAlchemy по вложенным ключам в JSONB

У меня есть поле JSONB, которое иногда имеет вложенные ключи. Пример:

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

If I do

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

Я получаю эту запись.

Как я могу найти наличие вложенного ключа? ("a simple text")


python postgresql sql sqlalchemy jsonb
person Boaz    schedule 29.01.2015    source источник

Ответы (2)


arrow_upward
15
arrow_downward

С SQLAlchemy для вашей тестовой строки должно работать следующее:

class TestMetadata(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)
    metadata_item = Column(JSONB)

согласно документации SQLAlchemy 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}'}
person van    schedule 03.02.2015
comment
Потрясающе... ИМХО, неясно, что индекс пути - это вложенный JSON, но, может быть, это только у меня так. Кстати, для проверки значения я бы предпочел использовать внутренний: .filter(TestMetadata.metadata_item[key].astext == value) Где ключ - это кортеж - person Boaz; 04.02.2015
comment
Конечно, я думал, что ваш вопрос больше касается проверки существования, а не проверки значения. - person van; 04.02.2015
comment
вы совершенно правы... Мне нужны они оба (и, по-видимому, реализация отличается, потому что has_key не поддерживает кортеж) - person Boaz; 04.02.2015
comment
Как также выбрать явное значение None если он существует: {'nested_field': None}? - person Joost Döbken; 11.04.2016
comment
@van есть идеи, как можно проверить, является ли ключ верхнего уровня больше или меньше значения? И еще, для вложенного ключа? - person Augiwan; 18.04.2016
comment
@UGS: ознакомьтесь с официальной документацией по SA в JSONB. . Важно то, что вам нужно cast(...) ввести правильный тип (Integer, Numeric), чтобы сравнение работало. Я могу представить, что фильтр для вложенного ключа будет q = session.query(...).filter(MyObject.my_json_attribute[('root_key', 'sub_key1', 'sub_key2')].cast(Integer) > 33). Для ключа верхнего уровня будет один root_key вместо 3 в приведенном выше примере. - person van; 19.04.2016

arrow_upward
1
arrow_downward

Этот запрос проверяет наличие вложенного поля с ? оператор после извлечения вложенного объекта JSON с помощью -> оператор:

SELECT EXISTS (
   SELECT 1 
   FROM   testmetadata
   WHERE  metadata_item->'nested_field' ? 'a simple text'
   );

Обратите внимание, что простой индекс GIN не поддерживает этот запрос. Вам понадобится индекс выражений на metadata_item->'nested_field', чтобы сделать это быстро.

CREATE INDEX testmetadata_special_idx ON testmetadata
USING gin ((metadata_item->'nested_field'));

В руководстве есть пример для аналогичный случай.

person Erwin Brandstetter    schedule 03.02.2015