DbVisualizer

Posted on Sep 28, 2023 • Originally published at dbvis.com

Bridging the Gap Between SQL and NoSQL in PostgreSQL with JSON

PostgreSQL, a popular open-source relational database system can be used as a hybrid database to handle both structured and semi-structured data. PostgreSQL's support for JSON and JSONB object types allows for flexible data modelling and can solve many of the same problems that NoSQL databases are made to address.

Tools used in this tutorial

DbVisualizer, top rated database management tool and SQL client
The PostgreSQL Database

The rise of digital technology in this age has made data a pivotal component for businesses, leading to the need for efficient database management systems. Powerful open-source relational database management systems like PostgreSQL is a robust database management system, while NoSQL databases like MongoDB offer non-relational data models. Choosing between SQL and NoSQL databases is a complex decision, as each has its benefits and drawbacks. This blog will provide an overview of these features and explain their benefits and at the end of this blog, readers will have a better understanding of PostgreSQL's NoSQL capabilities and how they can be used to build more flexible and dynamic data paradigms using tools like DbVisualizer.

SQL vs. NoSQL

Relational and non-relational database management systems use various data models, query languages, and structures. While NoSQL databases are non-relational and employ various data models, SQL databases are relational database management systems. One key difference between the two is how they handle data storage. SQL databases use a relational database system: data is stored in structured tables, with columns representing specific types of data and rows representing individual records.

For example, the following table could store information about employees in a SQL database:

ID	Name	Phone	Nationality	Address
001	Bob Lewis	323-747-7152	Ghanaian	East Legon Ave.
002	Sally Fred	313-652-8189	Belgian	Sacresandes St 1
003	Jane Doe	212-456-374	Swedish	Nacka St

Contrarily, NoSQL databases use a variety of data models including key-value, document-oriented databases, etc. For example, a document-oriented database might store the employees’ data in the following JSON document format:

1 {
2    "id": 001,
3    "name": "Bob Lewis",
4    "phone": "323-747-7152",
5    "nationality": "Ghanaian"
6 }

Another difference between SQL and NoSQL databases is their query languages. NoSQL databases typically use query languages specific to the data model being used, such as MongoDB's query language for document-oriented databases. For example, a MongoDB database might use the following query to retrieve all employee documents from the table above:

1 db.customers.find();

In contrast, SQL databases such as PostgreSQL use a query language that is supported by relational databases. A SQL database might use the following query to retrieve all employee records from the table above:

1 SELECT * FROM employees;

Specific Features of PostgreSQL Also Applicable to NoSQL

PostgreSQL is designed to manage structured data using a traditional relational database model. However, PostgreSQL has some NoSQL features, such as support for JSON, and other flexible data types, and allows users to run queries using the JSONB data type which allows for more flexible and dynamic data modelling by offering more advanced indexing and querying capabilities than traditional search. These NoSQL-like functionalities make it a hybrid database that can handle both structured and semi-structured data.

JSON and JSONB Object Types Can Help Bridge the NoSQL Gap

JSON and JSONB are both data formats that are commonly adopted for storing and sharing structured data. While the JSON and JSONB data types were relatively new to the PostgreSQL world, JSON allows PostgreSQL to compete with NoSQL databases like MongoDB because it enables PostgreSQL to store and manage semi-structured data, which was previously not possible with traditional relational databases. Although there are many similarities between the two formats, there are some key differences between them. JSON is commonly used to exchange data between web services and clients, as well as to store configuration files. The JSON specification supports many data types, including strings, numbers, booleans, arrays, and objects.

The JSONB format, however, is a binary format that allows JSON data to be stored and retrieved efficiently. It is a PostgreSQL-specific data type and supports all of the same data types as JSON, but it also provides additional features like indexing and searching JSON data. When a database query is run, the database engine scans every table in the database for the target data. This traditional process can be slow. When this happens, Jsonb can be employed to ease up the data retrieval process.

JSON and JSONB: What are the Differences?

To help you decide, this comprehensive article on JSON vs. JSONB in PostgreSQL can be helpful to learn about the advantages the JSONB type in PostgreSQL offers over JSON for data storage and retrieval. JSON and JSONB differ primarily in the way they store data behind the scenes. Jsonb is inherently more efficient than json. When it comes to choosing between JSON and JSONB for your database needs, there are several factors to consider. Let us now dig into how to work with both object types in PostgreSQL.

Working with JSON Data in PostgreSQL

PostgreSQL provides native support for JSON and JSONB data types, which allows users to store and manipulate JSON data directly within the database. Here are some examples of how to work with JSON data in PostgreSQL:

Creating a Table with a JSON Column

1 CREATE TABLE employees (
2    id SERIAL PRIMARY KEY,
3    name TEXT
4    phone VARCHAR
5    nationality TEXT
6    data JSON
7 );

This query creates a table called ‘employees’ with the following columns:

Id (unique key)
Name
Phone
Nationality
Data (JSON column)
Running a Query in DbVisualizer.
Inserting Data into a JSON Column

1 INSERT INTO employees (name, nationality, data) VALUES ('Sally', 'Ukrainian', '{"name": "Sally", "nationality": "Ukrainian"}');

This is one example of how to work with JSON data in PostgreSQL, and there are various ways to query, manipulate, and extract data from JSON columns in PostgreSQL. Next, we will explore how to query JSON data.

Querying JSON Data

Querying json data in PostgreSQL is very flexible and powerful, allowing you to search for data at any level of the json structure. Let’s look at some things you can accomplish when querying json data:

Retrieving a single JSON value

To retrieve a single json value from a column in a table, you can use the -> or ->> operator. The -> operator returns the value as a json object, while ->> operator returns the value as a string.

1 SELECT data->'name' as name, data->>'nationality' as nationality
2 FROM employees
3 WHERE data->>'name' = 'Martin';

In the example above, we retrieve the name and nationality values from a json object stored in the data column of the employees table. We also filter the results to only include rows where the name value is equal to 'Martin'.

Querying JSON arrays

To query json arrays, you can use the -> or ->> operator to access the values of the array, and then use the json_array_elements function to expand the array into separate rows.

1 SELECT json_array_elements(data->'phone') AS phone
2 FROM employee
3 WHERE data->'name' = 'Martin';

Here, we retrieve the phone values from a json array stored in the data field of the employees table. We also use the json_array_elements function to expand the array into separate rows, so that we can retrieve each phone as a separate value.

Querying nested JSON objects

Let's assume we have a table called ‘employees’, with the ID and data columns as shown below:

ID	Data
001	{"name": "Jane", "age": 35, "address": {"street": "110 Main St", "city": "Queens", "state": "NY"}}
002	{"name": "Bob", "age": 28, "address": {"street": "619 Elm St", "city": "Bronx", "state": "NY"}}
003	{"name": "Eve", "age": 42, "address": {"street": "166 Oak St", "city": "Seattle", "state": "WA"}}

To query nested json objects, you can use the -> or ->> operator multiple times to access the nested values. In the example below, we retrieve the city and state values from a nested json object stored in the address field of the data column in the table. The results are also filtered to only include rows where the state value is equal to 'NY'.

1 SELECT data->'address'->>'city' AS city, data->'address'->>'state' AS state
2 FROM employees
3 WHERE data->'address'->>'state' = 'NY';

The query above would return:

City	State
Queens	NY
Bronx	NY

This is pretty much everything you can do with JSON. Now let’s look at the JSONB data type.

Working with JSONB Data in PostgreSQL

Provided you’re working on a table that does not have a column for jsonb data, you can add a jsonb column to the table with the following query:

1 ALTER TABLE employees ADD COLUMN log jsonb;

After successful addition of the jsonb column, users will now be able to store and manipulate jsonb data directly within the database. We will provide you with an example of how the JSONB data type works in the real world below.

Indexing JSONB Data using the ‘GIN’ Index

To speed up a data retrieval process, an index can be created on one or more columns of a table. An index can be created like so:

1 CREATE INDEX employees_data_gin_idx ON employees USING gin (data jsonb_path_ops);

The query creates a gin index on the data column of the employees table which allows for efficient indexing and searching of jsonb data.

Extracting Elements of a JSONB Array

Another way of working with JSONB data is to use the jsonb_array_elements function to extract the elements of a JSONB array.

1 SELECT value ->> 'name', value ->> 'phone'
2 FROM employees, jsonb_array_elements(data -> 'contacts')
3 WHERE value ->> 'type' = 'work';

The query above returns the name and phone number of all the work contacts stored in the "data" column of the "employees" table. The "contacts" key in the JSONB object is an array of objects, and we use jsonb_array_elements to extract the individual objects and their properties. The condition value ->> 'type' = 'work' assumes that the JSONB objects in the "contacts" array have a "type" key that indicates whether the contact is for work or personal use.

To ensure PostgreSQL performs optimally, users should examine the performance implications of these features.

Cases where PostgreSQL's NoSQL features can be particularly useful

Schema design flexibility: A key feature of NoSQL databases is their flexibility in schema design; they allow for dynamic, schemaless data structures. The JSON and JSONB data types in PostgreSQL also allow for a similar level of flexibility in schema design, since they can store semi-structured or unstructured data within a single column. Below is an example:

1 CREATE TABLE employees (
2    id SERIAL PRIMARY KEY,
3    name TEXT NOT NULL,
4    info JSONB
5 );
6 INSERT INTO employees (name, info)
7 VALUES ('Employee A', '{"email": "employeea@hotmail.com", "address": {"street": "109 Brandon St", "city": "East Leg Ave", "state": "Accra", "zip": "00233"}}');

In this example, the employees table has a JSONB column called info that stores semi-structured data for each employee. This makes it easy to store and query a wide range of employee information in a flexible, scalable way.

Horizontal scaling across multiple nodes: NoSQL databases are mostly known for their ability to scale horizontally across multiple nodes. However, with PostgreSQL, it's also possible to achieve similar scalability through the use of partitioning and replication. For an example explaining how partitioning can be used in PostgreSQL to improve scalability, let us assume we have a database and table called Sales with the following columns:

ID (primary key)
Date
Customer ID
Amount
Product ID

We will now create a table partitioned by range for values ranging from 2020 to 2021, and then another table for values ranging from 2021 to 2022.

1 CREATE TABLE sales (
2    id SERIAL PRIMARY KEY,
3    date DATE NOT NULL,
4    customer_id INTEGER NOT NULL,
5    amount NUMERIC(10, 2) NOT NULL,
6    product_id INTEGER NOT NULL
7 )
8 PARTITION BY RANGE (date);
9 CREATE TABLE sales_2020 PARTITION OF sales FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
10 CREATE TABLE sales_2021 PARTITION OF sales FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

In this example, the sales table is partitioned by date, allowing data to be stored across multiple tables based on a specific range of dates. This can improve query performance and scalability by distributing data across multiple nodes.

A Comparison of JSON in Postgres and NoSQL Databases

PostgreSQL being a NoSQL database does not make it less advantageous or valuable. Although PostgreSQL is typically categorised as a relational database management system (RDBMS), its support for JSON and JSONB data types makes it able to store and query semi-structured and unstructured data. As a middle ground between traditional relational databases and NoSQL databases, it allows for schema-less data storage and queries while maintaining ACID compliance and SQL query capabilities.

Here are some differences between using JSON in SQL (PostgreSQL) and a NoSQL database (Mongo DB):

Feature	PostgreSQL	MongoDB
Scalability	Can scale horizontally but may require additional tools or technologies	Designed for horizontal scalability
Transactions	Supports transactions and ACID compliance	Lacks support for ACID compliance and uses eventual consistency
Querying	Supports SQL querying of JSON data using JSON functions and operators	Uses document-based query language or API for querying
Data Model	Relational database management system with support for structured and semi-structured data types	Document-oriented NoSQL database management system

Conclusion

In this blog, we have learnt about what JSON and JSONB object types are, how to work with JSON and JSONB data in Postgres, and how to use them to solve most of the problems solved by NoSQL databases such as MongoDB and the like. While NoSQL databases like MongoDB are known for their flexibility and ability to handle unstructured data, some relational databases like PostgreSQL offer support for JSON and JSONB data types along with its relational database management system features, thus providing a practical and fully-fledged alternative to NoSQL databases. With its SQL-based querying capabilities and ACID compliance, PostgreSQL is an attractive option for developers who need NoSQL scalability, but still require the power of SQL.

DbVisualizer of course supports both JSON and JSONB for Postgres. You can download DbVisualizer and get started for free. Thanks for reading! We hope that you found this article helpful.

About the author

Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn.

DEV Community