quick - Filtro SQLAlchemy según claves anidadas en JSONB

sqlalchemy tutorial español

Question

Tengo un campo JSONB que a veces tiene claves anidadas. Ejemplo:

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

Si lo hago .filter(TestMetadata.metadata_item.has_key(nested_field))
Obtengo este registro.

¿Cómo puedo buscar la existencia de la clave anidada? ( "a simple text" )

Answer 1

Con SQLAlchemy, lo siguiente debería funcionar para su cadena de prueba:

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

según la documentación de SQLAlchemy de JSONB (búsqueda del ejemplo de operaciones de índice de ruta ):

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

que debería generar el SQL continuación:

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

Answer 2

Esta consulta prueba la existencia del campo anidado con el ? operador , después de extraer el objeto JSON anidado con el operador -> :

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

Tenga en cuenta que un índice GIN simple no admite esta consulta. Necesitarías un índice de expresión en metadata_item->''nested_field'' para hacer esto rápido.

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

Hay un ejemplo en el manual para un caso similar.