SQLAlchemy filter by json field

In this article, we will be discussing the SQLAlchemy filter by JSON field using Python. Before we begin the introduction, to the topic we will be discussing the basics of how to filter by JSON fields using a few examples and a brief introduction to SQLAlchemy, JSON, and JSON fields.

Required Package

pip install SQLAlchemy

What is SQLAlchemy? 

SQLAlchemy is a Python library that provides a nice API for interacting with databases. It has a lot of features, including a powerful ORM (Object-Relational Mapper) that allows you to map Python objects to database tables and vice versa.

Using SQLAlchemy can make it easier to work with databases in your Python code because it provides a consistent interface for interacting with different types of databases by abstracting some of the queries. For more information refer to this article many on SQLAlchamey.

What is JSON?

A JSON file is a file that stores simple data structures and objects in JavaScript Object Notation(JSON) format, which is a standard data interchange format. It is primarily used to transmit data between servers and web applications as an alternative to XML.

What is a JSON field?

A JSON field is a field in a database table that is designed to store JSON data. JSON field is generally used to store complex, multi-level data structures that cannot be easily represented with the traditional database schema. It is beneficial when the data structure is not known in advance or is likely to change over time.

In a database table, a JSON field is usually defined as a column of type JSON or a JSONB. The JSON type stores the JSON data type as plain string, while the JSONB stores it in binary format which is much more efficient to query but is more challenging to read and interpret.

How to query using JSON fields?

To filter a query using a JSON field with SQLAlcoperatoremy, we make use of the filter method and specify a condition that uses the @ operator. The @ checks if the left JSON value is a superset of the right JSON value the other method is shown below in the example. I have used Mysql which is an RDBMS. 

Creating a Database to Filter by JSON field

This code creates a database connection to a MySQL server using the SQLAlchemy library. It then creates a table called employees with two columns, id, and data. The data column is of type JSON, which allows for the storage of JSON data. The code then creates a session, adds a row to the employee table with a JSON object containing first_name, last_name, and age, and commits the changes to the database. Finally, it queries the table for the row where the first_name value is joseph and prints the id of the returned row. The session is closed at the end.

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, JSON
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Connects to database engine used mysql
engine = create_engine('mysql://roots:PASSWORD@localhost:3306/trial')
Base = declarative_base()

# Creating a table in database with two columns id and data
# for this example the entire json file is stored in data
class MyTable(Base):
    __tablename__ = 'employees'
    id = Column(Integer, primary_key=True)
    data = Column(JSON)


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# Insert a row with a JSON column
session.add(MyTable(data={"first_name": "joseph",
                          "last_name": "matthew", "age": "18"}))
session.commit()

# Query the table using the JSON column
result = (
    session.query(MyTable)
    .filter(MyTable .data["first_name"] == "joseph")
    .all()
)

for result in result:

    print(result.id)  # prints 1

Output:

Inserting Another Row 

In this example, we will be adding another row and querying all data from the tables. It adds another row to the table with a different JSON object containing first_name, last_name, and age, and commits the changes to the database. Then it queries all rows in the table and prints the id and data for each returned row.

from sqlalchemy.orm import sessionmaker
from Creation import engine, MyTable

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Update the values into the table
session.add(MyTable(data={"first_name": "martha",
                          "last_name": "stuart", "age": "45"}))
session.add(MyTable(data={"first_name": "boris",
                          "last_name": "johnson", "age": "35"}))
session.add(MyTable(data={"first_name": "vishal",
                          "last_name": "kamat", "age": "25"}))
session.commit()

# Query all rows in the table
results = session.query(MyTable).all()
for result in results:
    print(result.id, result.data)

session.commit()

Output:

Deleting Row

In this example, we will be deleting a row of data based on the id. This code deletes a row from the employee's table where the 'id' column is equal to 2. It then commits the changes to the database and closes the session. Below is the code for Deletion.py.

from sqlalchemy.orm import sessionmaker
from Creation import engine, MyTable

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Update the values into the table
# Delete a row based on id
session.query(MyTable).filter(MyTable.id == 2).delete()
session.commit()


# Query all rows in the table
results = session.query(MyTable).all()
for result in results:
    print(result.id, result.data)

session.commit()
# closes the session here
session.close()

The Output for Deletion.py