SQLAlchemy: filtering on values stored in nested list of the JSONB field

by

[*]

SQLAlchemy’s JSONB type has the contains() method for the @> operator in Postgresql. The @> operator is used to check if the left value contains the right JSON path/value entries at the top level. In your case

data @> '{"nested_list": [{"nested_key": "one"}]}'::jsonb

Or in python

the_value="one"

Session().query(Item).filter(Item.data.contains(
    {'nested_list': [{'nested_key': the_value}]}
))

The method converts your python structure to suitable JSON string for the database.

In Postgresql 12 you can use the JSON path functions:

import json

Session().query(Item).\
    filter(func.jsonb_path_exists(
        Item.data,
        '$.nested_list[*].nested_key ? (@ == $val)',
        json.dumps({"val": the_value})))

More Related Contents:

  1. Updates to JSON field don’t persist to DB
  2. How to serialize SqlAlchemy result to JSON?
  3. How to write DataFrame to postgres table
  4. CS50: LIKE operator, variable substitution with % expansion
  5. SQLAlchemy create_all() does not create tables
  6. Executing multiple statements with Postgresql via SQLAlchemy does not persist changes
  7. Django JSONField inside ArrayField
  8. How to create an SQL View with SQLAlchemy?
  9. Generate sql with subquery as a column in select statement using SQLAlchemy
  10. Complex foreign key constraint in SQLAlchemy
  11. Connection problems with SQLAlchemy and multiple processes
  12. How to upsert pandas DataFrame to PostgreSQL table?
  13. SQLAlchemy multiple foreign keys in one mapped class to the same primary key
  14. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  15. SQLAlchemy – performing a bulk upsert (if exists, update, else insert) in postgresql
  16. Strange SQLAlchemy error message: TypeError: ‘dict’ object does not support indexing
  17. Creating partial unique index with sqlalchemy on Postgres
  18. SQLAlchemy func.count on boolean column
  19. How to set connection timeout in SQLAlchemy
  20. How to prevent UUID primary key for new SQLAlchemy objects being created with the same value
  21. Python/Json:Expecting property name enclosed in double quotes
  22. Can’t subtract offset-naive and offset-aware datetimes
  23. error installing psycopg2, library not found for -lssl
  24. How to get JSON from webpage into Python script
  25. Django REST Framework POST nested objects
  26. Python: Trying to Deserialize Multiple JSON objects in a file with each object spanning multiple but consistently spaced number of lines
  27. How to return data in JSON format using FastAPI?
  28. sqlalchemy: create relations but without foreign key constraint in db?
  29. Keep a datetime.date in ‘yyyy-mm-dd’ format when using Flask’s jsonify
  30. How to perform filtering with a Django JSONField?

Leave a Comment