sql

CRUD operations in postgres jsonb

Hello. In this tutorial, we will learn how to handle crud operations on a jsonb column in the postgres database.

1. Introduction

SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. A relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time SQL manages a large amount of data that is written and read simultaneously and any query that reaches the SQL server is processed into three parts –

  • Parsing – Process to check the SQL query syntax
  • Binding – Process to check the SQL query semantics
  • Optimization – Process to generate the SQL query execution plan

1.1 Usage of SQL

Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too –

  • Execute queries against the database
  • Retrieve data from the database
  • Insert new records into the database
  • Update existing records into the database
  • Created stored procedures, functions, and materialized views in the database
  • Create users and grant permissions
  • Set permissions on tables, stored procedures, functions, and materialized views

1.2 JSONB column

JSONB column stands for JSON binary. It is a datatype that helps store JSON structure in the table column. It supports indexing and is very efficient at parsing and querying the JSON data. It offers both advantages and disadvantages and is given below –

  • More efficient and faster to process
  • Supports indexing
  • Simple schema designs
  • Slower input due to conversion overheads
  • Takes more disk space due to a larger footprint but is not always

2. CRUD operations in postgres jsonb

Let us dive into some practice implementation on the postgres database.

2.1 Pre-requirement – Postgres Setup

Usually, setting up the database is a tedious step but with technological advancements, this process has become simple with the help of Docker. Readers can watch the video available at this link to understand the Docker installation on Windows OS. Open the terminal and trigger the following commands to get the postgres up and running on the local machine.

Docker commands

-- command to run postgres on docker --
-- remember to change the password --
docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres

-- command to stop the Postgres docker container --
docker stop postgres

-- command to remove the Postgres docker container --
docker rm postgres

Remember to enter the password of your choice. If everything goes well the postgres database server would be up and running on a port number – 5433 and you can connect with the pgAdmin GUI tool for connecting to the server.

Fig. 1. Postgres on Docker

2.2 Creating a table

To practice the jsonb datatype I will create a table named demo in the learning database. The table will have an id (primary key) and jsonb columns.

Create query

-- drop table if exists
drop table demo;

-- create a table with jsonb column
create table demo (id serial primary key, data jsonb);

-- describe table structure
select column_name, data_type from information_schema."columns" c where c.table_name = 'demo';

Once the table is created, use the information_schema select query to view the created table schema.

Fig. 2: Table schema

2.3 Inserting records into a table

Once the table is created add some json data to the table.

Insert query

-- insert dummy records into the table
insert into demo (data) values ('{"title": "PostgreSQL for Beginners", "type": ["Programming", "Computer"], "published": true}');
insert into demo (data) values ('{"title": "Harry Potter", "type": ["Novel", "Fantasy"], "published": true}');
insert into demo (data) values ('{"title": "The Pragmatic Programmer", "type": ["Programming", "Computer"], "published": false}');
insert into demo (data) values ('{"title": "The adventure of Sherlock holmes", "type": ["Novel", "Adventure"], "published": true}');
insert into demo (data) values ('{"title": "Learning Java", "type": ["Programming", "Computer"], "published": false}');

-- view all records in table
select * from demo;

Once the data is inserted, use the select star (*) query to view the inserted records or refer to Section 2.4.

Fig. 3: Inserted records

2.4 Viewing the records

To view the inserted data we will use the select query. The below script shows the different flavors of the select operation while using it with the jsonb column and you’re free to change them as per your wish.

Select query

-- view record(s) based on a condition
select * from demo where data ->> 'title' like '%Postgre%';
select * from demo where data ->> 'type' like '%Programming%' and data ->> 'published' = 'false';
select * from demo where data ->> 'published' = 'true';

select data -> 'title' as title from demo;
select data -> 'title' as title from demo where data ->> 'published' = 'true';
select data -> 'title' as title, data -> 'type' as genre from demo where data ->> 'published' = 'false';

-- view all records in table
select * from demo;

If everything goes the output of the query will be shown on the console. The below picture uses the select start to show all the inserted records.

Fig. 4: Viewing all inserted records

2.5 Updating a record

The important part of any crud application is the record update. The below SQL script will update the record for the title that contains the Harry keyword.

Update query

-- update record(s) based on a condition
update demo set data = data || '{"type": ["Novel", "Fantasy", "Crowd favourite"]}' where data ->> 'title' like '%Harry%';

select * from demo where data ->> 'title' like '%Harry%';

The below picture shows the updated record.

Fig. 5: View record after update

2.6 Delete record

Use the below sql script to clean up a record or all records from the demo table.

Delete query

-- delete record(s) based on a condition
delete from demo where data ->> 'published' = 'true';

select * from demo;

-- delete all records
delete from demo;

The below picture shows the leftover records after the selective delete.

Fig. 6: Delete record

3. Summary

In this tutorial, we learned the brief to SQL, jsonb column, and simple crud operations via the SQL queries. You can download the SQL scripts and docker commands used in this tutorial from the Downloads section.

4. Download the Scripts

This was a tutorial to perform crud operations on a jsonb column via the SQL queries.

Download
You can download the full source code of this example here: CRUD operations in postgres jsonb

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button