SQLAlchemy: retrieve records by exact matching on keys in nested JSONB field

Question

Let's say I have a table named Table, which contains a JSONB field data. One of the records (record 1) has the following JSON object stored there:

{
    "A": {},
    "B": {}
}

Another record (record 2) has the following JSONB object stored:

{
    "A": {},
    "B": {},
    "C": {}
}

I am struggling to define a way to find records by doing an exact filtering on the keys in the data field. For example, I would like to retrieve the record with only the keys "A" and "B", in the data field, i.e. record 1.

I have tried the SQLAlchemy's JSONB type comparator methods such as the contains() method but this would return both records in case of the following Python query:

Session().query(Table).filter(Table.data.contains({"A": {}, "B": {}})).all()

Is it possible to achieve an exact filtering on (a list of) keys with the available SQLAlchemy filters? Any thoughts are welcome. Many thanks!

Does this answer your question? [Flask SQLAlchemy query, specify column names](https://stackoverflow.com/questions/11530196/flask-sqlalchemy-query-specify-column-names) — sahasrara62, Nov 06 '20 at 13:24
@sahasrara62 They are different questions so unfortunately it does not answer my question. I am looking specifically for a way to check if the keys in a JSONB field exactly match with a list of keys. — Paul, Nov 06 '20 at 14:39
so you are trying to filter out data from the data object (here json ) stored in the database ? i dont think you can directly do that, you need to fetch all first then iterate through them and apply the condition — sahasrara62, Nov 06 '20 at 14:45
Not exactly filtering out data from the data object (jsonb) but rather filtering records based on the keys in the data object. So basically I am looking for something along the lines of the follow filtering functionality: ```filter(Table.data.keys() == ['A', 'B'])```. This does not work because the JSONB object data does not have a function ```keys()``` — Paul, Nov 06 '20 at 16:03

score 0 · Answer 1 · answered Nov 07 '20 at 18:04

The ? operator returns true if the string exists as a top-level key.

The - operator deletes key/value pairs from the left operand.

You can combine these like this:

select data from Table 
where data ? 'A' and data ? 'B' and (data - 'A') - 'B' = '{}';

In English, get records that

contain 'A' and 'B' keys: data ? 'A' and data ? 'B'
are empty if you remove 'A' and 'B' keys (ie contain no other keys): (data - 'A') - 'B' = '{}'

SQLAlchemy: retrieve records by exact matching on keys in nested JSONB field

1 Answers1