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.
Python
from sqlalchemy import create_enginefrom sqlalchemy import Column, Integer, String, JSONfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmaker# Connects to database engine used mysqlBase = declarative_base()# Creating a table in database with two columns id and data# for this example the entire json file is stored in dataclass 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 columnsession.add(MyTable(data={"first_name": "joseph", "last_name": "matthew", "age": "18"}))session.commit()# Query the table using the JSON columnresult = ( session.query(MyTable) .filter(MyTable .data["first_name"] == "joseph") .all())for result in result: print(result.id) # prints 1 |
Output:
The table is shown in Mysql CLi
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.
Python
from sqlalchemy.orm import sessionmakerfrom Creation import engine, MyTable# Create a sessionSession = sessionmaker(bind=engine)session = Session()# Update the values into the tablesession.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 tableresults = session.query(MyTable).all()for result in results: print(result.id, result.data)session.commit() |
Output:
Output of Update.py
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.
Python3
from sqlalchemy.orm import sessionmakerfrom Creation import engine, MyTable# Create a sessionSession = sessionmaker(bind=engine)session = Session()# Update the values into the table# Delete a row based on idsession.query(MyTable).filter(MyTable.id == 2).delete()session.commit()# Query all rows in the tableresults = session.query(MyTable).all()for result in results: print(result.id, result.data)session.commit()# closes the session heresession.close() |
The Output for Deletion.py
