CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemyjsonb
Boaz
Boaz

Reputation: 5084

SQLAlchemy filter according to nested keys in 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"}

If I do

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

I get this record.

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

Upvotes: 8

Views: 11597

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

Update for Postgres 12+

Use the jsonpath operator @?:

SELECT EXISTS (
   SELECT FROM testmetadata
   WHERE  metadata_item @? '$.nested_field."a simple text"'
   );

Returns true if the jsonb value has that nested key.

Can be supported by a plain GIN index on the jsonb column (metadata_item) or a more specialized index with the jsonb_path_ops operator class. See:

Original answer for older versions

This query tests for existence of the nested field with the ? operator, after extracting the nested JSON object with the -> operator:

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

A plain GIN index does not support this query. You would need an expression index on metadata_item -> 'nested_field' to make this fast.

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

There is an example in the manual for a similar case.

Upvotes: 1

van
van

Reputation: 76962

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

Upvotes: 16

Related Questions