Update Single Element in JSONB Column with Sqlalchemy

In this article, we have covered how to update a single element or a list of elements in a JSONB column of a PostgreSQL database table using the SQLAlchemy core using Python. By using the jsonb_set() function and parameterized queries, we can update the desired elements in a secure and efficient way.

Required modules:

pip install SQLAlchemy
pip install psycopg2-binary

What is JSONB

JSONB is a PostgreSQL data type that allows you to store JSON data in binary representation. It offers a versatile method for storing and manipulating complicated data structures, making it perfect for dealing with huge and unstructured datasets. JSONB columns in databases are commonly used to store sophisticated data structures in today’s data-driven environment. JSONB columns enable the storage and manipulation of JSON objects within a PostgreSQL database. However, altering specific items inside a JSONB column can be difficult, particularly when dealing with big datasets. In this post, we will look at how to use the SQLAlchemy core to update a single element or a list of elements in a JSONB column of a PostgreSQL database table.

This article will show you how to use the SQLAlchemy core to update a single element or a list of elements in a JSONB column of a PostgreSQL database table. We will use SQLAlchemy’s core library rather than the ORM, which provides a lower-level interface to communicate with the database.

Creating a table

Our first step is to create a table that will hold our JSONB data. We will create a table named “employees” with two columns: “id” and “info”. The “info” column is a JSONB type that contains information about the employee.

We first import the necessary modules. We then create a database engine that connects to our PostgreSQL database. We then create a metadata object and use it to define our table “employees”. We define two columns: “id” and “info”. The “id” column is an integer column and is the primary key of our table. Whereas, the “info” column is a JSONB-type column that will hold our JSON data. We then call the create_all() method of the metadata object to create our table in the database. Finally, we create a metadata object and use it to define the “employees” table. There will be two columns, “id” and “info,” in this table. The primary key for our table is located in the “id” column, which is an integer column. The “info” column is a JSONB type column, where we’ll keep our JSON data. To proceed with the database table creation, we then call the create all() method on the metadata object. 

Python3

# code
from sqlalchemy import create_engine, Table, Column, Integer, JSON
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.sql import text
from sqlalchemy import MetaData

engine = create_engine
    ('postgresql://vikadmin:dbpass@localhost:5432/vikashdb')
metadata = MetaData()

# Defining the Table schema, using extend_existing
# will allow to rerun the code
# when table already exist in the database.
employeest = Table('employeest', metadata,
                   Column('id', Integer,
                          primary_key=True),
                   Column('info', JSONB),
                   extend_existing=True)
metadata.create_all(engine)

Inserting Dummy Data

Now that we have created our table, we can insert some dummy data into it. The following code will insert a single record into the “employees” table you can add more.

In the code, we create an insert_query object that inserts a single record into the “employees” table. We define the values of the “id” and “info” columns in the values() method. We then connect to the database using the engine.connect() method and execute the query using the execute() method of the connection object. Finally, we close the connection using the close() method.

Python3

# Define the insert query with the data you want to insert in the table
insert_query = employeest.insert().values(id=10, info={
    "name": "Jon",
    "age": 33,
    "job": {"title": "Software Engineer", "level": "three"}
})
print(insert_query)
conn = engine.connect()
result = conn.execute(insert_query)
print('Insert query results:', result.rowcount)
conn.commit()

Fetching the Data from the Table

We can fetch all the data from the table to verify the row we inserted is present in the table. 

Python3

# Print the data we inserted, along with previous rows
select_query = employeest.select()
conn = engine.connect()
print('Connection status:', conn.closed)
result = conn.execute(select_query)
print('Select query results:', result.fetchall())

for row in result:
    print(row)

Update a Single Element with Matching ID

We will now update a single element in the JSONB column matching a specific ID. In the following code, we will update the value of the “job_title” key in the “info” column of the record with ID 1.

In the code, we create a update_query object that updates the “info” column of the “employees” table. We use the jsonb_set() function to update the value of the “job_title” key in the JSONB object. We are using the jsonb_set() function to update the value of the “job_title” key in the “info” column of the “employees” table. The jsonb_set() function takes three arguments: the JSONB object, the path to the element we want to update, and the new value we want to set. We are also using a parameterized query to prevent SQL injection.

Python3

# Update the title of the employee
update_query = text("""
    UPDATE employeest
    SET info = jsonb_set(info,
    '{job, title}', '"Data Scientist"', false)
    WHERE id = 10;
""")
# Print the query for verification
print(update_query)
# Execute the update query command
result = conn.execute(update_query)
print('update query results:', result.rowcount)
# Commit will allow to save the changes in the database
conn.commit()

Updating a list of elements matching an ID:

In the code, we are using the jsonb_set() function to update the value of the “name” key in the second element of the “projects” list in the “info” column of the “employees” table.

We first create a text object containing our SQL query and use the jsonb_set() function to update the value of the desired element in the JSONB object. We then use a parameterized query to pass the ID value to the query, Here, Colon (:id) is used in HQL Hibernate Query Language to signify that there is a parameter involved. Finally, we execute the query using the execute() method of the connection object.

Python3

update_query = text("""
    UPDATE employees
    SET info = jsonb_set(info, '{projects, 1, name}', '"New Project Name"')
    WHERE id = :id
""")
conn = engine.connect()
conn.execute(update_query, id=1)
conn.close()

Output: