JSONPath in PostgreSQL: How To Query It In A Right Way?

How to query JSON in Postgres

Are you facing the issue where your website page is taking too long to load? Well, it seems to be a critical stage for the most visited page of your website. Looking out to find the solution, we came across PostgreSQL JSON functions.

PostgreSQL facilitates users with a robust and versatile JSON data storing and processing platform. PostgreSQL consists of two databases: JSON and JSONB. The document’s size and complexity depict the performance difference between both databases.

Now, the question arises: How to query JSON in Postgres?

In this article, we will give you an overview of JSON capabilities within PostgreSQL. Before we get started, let’s get familiar with the basics of JSON and PostgreSQL.

What Is JSON?

JSON (Javascript Object Notation), an open-standard file format that organizes data into key-value pairs is widely used for transmitting data in web services. It is considered the primary format as it is more lightweight than the older XML.

JSON string Example:

‘{“ID”: “124”, “name”: “Maria”, “age”:48", “Country”: “London”}’

It defines an object with 4 properties:

  • ID
  • Name
  • Age
  • Country

Each property has a value. You can get the data as an object by using a JavaScript program to parse the JSON string:

let personID = obj.ID;

let personCountry = obj.country;

There are many popular databases like MongoDB to store and process JSON data. Until recent times, JSON strings were considered normal text with no data types in RDBMS databases. PostgreSQL, an open-source RDBMS database, handles JSON data-type benefiting performance, functionality, and scalability.

PostgreSQL: First Choice Of Enterprise Developers

With over 30 years of ongoing development, PostgreSQL is a potent open-source object-relational database system that has built a solid reputation for dependability, feature robustness, and performance.

Due to its strong core technology, which includes MVCC (Multivariant Parallelism Control), which allows numerous readers and writers to operate on the system concurrently. Postgres has emerged as the top choice for businesses conducting complicated and volumetric high-data processes.

PostgreSQL has a remarkable capacity for efficiently and concurrently resolving many issues. That is why PostgreSQL continues to be used by industry behemoths like Yahoo!, Apple, Meta, significant telecommunications providers, and financial and governmental institutions.

According to Statista.com, PostgreSQL is featured as one of the most popular database management systems worldwide with a ranking score of 606.56.

JSON Data In PostgreSQL Working

Since the JSON data type was created, the PostgreSQL database has grown in popularity. In reality, when it comes to handling a lot of JSON data, PostgreSQL has been outperforming MongoDB. The PostgreSQL database allows the applications to store JSON strings in its default JSON format.

In PostgreSQL-9.2, the JSON data type was introduced, and afterward, substantial improvements were added. The JSONB data type was the most significant innovation in PostgreSQL 9.4. A more sophisticated JSON data type known as JSONB holds JSON data in binary format. This is the key improvement that significantly changed how PostgreSQL sought and handled JSON data.

Reasons To Store JSON in PostgreSQL

Schematic adaptability

Schema flexibility is one of the key benefits of utilizing the JSON format to store data. When your schema is fluid and constantly changing, storing your data as JSON can be helpful.

Stacked items

In some circumstances, it is simpler to handle nested objects (single or multi-level) in JSON rather than denormalizing the data into columns or several tables.

Synchronization with outside data sources

It may be a temporary repository while data is being digested into other sections of the system since an external system frequently provides data in JSON format.

Querying JSON in PostgreSQL

As discussed before, there are two JSON column data types: JSON and JSONB. Sometimes, the Postgres database requires JSONB to store data because of high data processing on JSON. So how we will do that? Well, it is exactly the same as JSON. Let’s clear your thoughts with an example.

Consider an ‘order sourcing’ application, and use JSONB to store a sequence of events.

CREATE TABLE orders (

id serial NOT NULL PRIMARY KEY,

info json NOT NULL

);

Two columns make up the orders table:

  • The primary key column for identifying the order is the id column.
  • The data is kept in the info column as JSON.

Now, we will insert the data into the JSONB column.

INSERT INTO orders (info)

VALUES(‘{ “customer”: “Maria Doe”, “items”: {“product”: “Beer”,”qty”: 5}}’),

(‘{ “customer”: “Williamson”, “items”: {“product”: “Chocos”,”qty”: 2}}’),

(‘{ “customer”: “David Blair”, “items”: {“product”: “Buns”,”qty”: 8}}’);

Further, use the SELECT statement to query JSONB data.

SELECT info FROM orders;

INFO

{ “customer”: “Maria Doe”, “items”: {“product”: “Beer”,”qty”: 5}

{ “customer”: “Williamson”, “items”: {“product”: “Chocos”,”qty”: 2}

{ “customer”: “David Blair”, “items”: {“product”: “Buns”,”qty”: 8}

A JSON-formatted result set is returned by PostgreSQL.

To help you query JSON data, PostgreSQL includes two native operators: -> and ->>.

The JSON object field returns the key by the operator ->.

SELECT info -> ‘customer’ AS customer

FROM orders;

Customer

“Maria Doe”

“Williamson”

“David Blair”

The JSON object field returns the text by the operator ->>.

SELECT info ->> ‘customer’ AS customer

FROM orders;

Customer

Maria Doe

Williamson

David Blair

Now, we will chain -> with ->> to retrieve a specific node. Here, we will retrieve the products.

SELECT info -> ‘items’ ->> ‘product’ as product

FROM orders

ORDER BY product;

Product

Beer

Chocos

Buns

This post consists of basic JSON functionalities. Dig deeper to find more PostgreSQL JSON Functions.

Altogether now,

I guess so far you are clear with your query, “How to query JSON in Postgres?”. PostgreSQL holds an excellent position to help you accelerate your website and take advantage of business growth with the combination of JSON and JSONB data types and all accessible operators.

That’s it for today. Stay connected for more Tech articles.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store