Working with Postgres JSON Query

Want to combine the versatility of the JSON data with the strength of relational databases? Or are you wondering how to perform Postgres JSON Query? If so, then you’ve landed in the right place!

This article will help you discover several operators and functions to query JSON data in PostgreSQL, as well as how to work it out with PostgreSQL JSON Query. Postgres allows you to combine relational and non-relational data effortlessly, giving users/applications flexibility in accessing and handling the data.

Mastering ETL: 9 Best Practices for a High-Performing ETL Process

Download for Free

It also includes native support for querying and processing JSON data. Read along to learn more about Postgres JSON Query.

What are PostgreSQL JSON Data types?

• JSON
• JSONB

These are two native data types in PostgreSQL that can be used to store JSON Data. The main distinction between them is that JSONB keeps data in a unique binary format while JSON stores data in a raw form. 

The input values for the JSON and JSONB data types are nearly identical. Efficiency is the main practical difference.

JSON data stores an exact copy of the input text, requiring processing functions to reparse each iteration. JSONB data is saved in a decomposed binary format, which makes it faster to process but slightly slower to input due to additional conversion overhead. Indexing is another feature that JSONB offers, which is a big plus.

JSON vs. JSONB

Most developers widely use JSONB because of its faster processing time & better performance. However, JSON performs better in the below-mentioned cases:

• JSON ingestion is quicker than JSONB, but JSONB is faster while conducting additional processing. When the formatting & the originality of the data is expected to be preserved (also known as whitespace), JSON is preferred.
• When there’s a need to retain duplicate keys.

Do you know? JSON might be a better alternative when you’re just absorbing JSON logs and not querying them.

Advantages of Postgres JSON Query

The best option for storing and processing your JSON Data is PostgreSQL. You can gain from inserting JSON into PostgreSQL for the reasons listed below:

• Developers frequently employ two or more data stores, such as PostgreSQL and MongoDB, and then send application data to those stores using a REST API. You can now overcome the complexity of using two database designs by inserting JSON into PostgreSQL.
• Your JSON data can be readily indexed and queried in PostgreSQL. You can experience a significant performance gain and scalability. The ability to search for data and access it using SQL commands is another benefit of inserting JSON into PostgreSQL.
• One of the most popular choices today is the JSONB Data type, an extension of JSON that guarantees high-quality, faster speed, and efficient storage.

Do you know? After PostgreSQL supported JSON Query, the data search performance was comparable and gave competition to the most widely used NoSQL databases, such as MongoDB.

Working with Postgres JSON Query

To work on Postgres JSON Query, you must first follow the below-mentioned sequence of action:

• Postgres JSON Query: Create Table
• Postgres JSON Query: Insert Data
• Postgres JSON Query: Query JSON Data
• Postgres JSON Query: JSON Operators & Functions

JSON File Format: 

Before diving deep into the working of Postgres JSON Query, let’s first understand the JSON File Format. The data in JSON File is stored in the form of “key: value” pairs using a simple format that is easy to understand.

For example, here’s a JSON file with an array called “Eatables” with these three objects:- type, name, and price.

{
	"Eatables": [

		{
			"type": "fruit",
			"name": "banana",
			"price": 1.49
		},
		{
			"type": "vegetable",
			"name": "tomato",
			"price": 0.64
		},
		{
			"type": "vegetable",
			"name": "potato",
			"price": 0.81
		}
	]
}

Note: In JSON, values must be one of the following data types:- string, number, object, array, boolean, null.

Postgres JSON Query: Create Table

Let’s get started by creating a new table with JSON data type in your PostgreSQL editor by writing the following code:

CREATE TABLE TakeOrder (
	id serial NOT NULL PRIMARY KEY,
	info json NOT NULL
);

This will create a table consisting of the next 2 columns:

• id Column: It acts as the primary key and identifies the order placed.
• info Column: It stores your data as JSON documents.

Postgres JSON Query: Insert Data

Now, after the successful creation of a Table in PostgreSQL. Use the INSERT function to insert JSON data into PostgreSQL tables. 

INSERT INTO TakeOrder (info)
VALUES('{ "customer": "Steve", "items": {"product": "Coffee","qty": 6}}');

The above code represents that Steve bought 6 cups of Coffee. Now, you can use the below code to insert multiple rows into your table at the same time:

INSERT INTO orders (info)
VALUES('{ "customer": "Edward", "items": {"product": "Bread","qty": 1}}'),
      ('{ "customer": "Bella", "items": {"product": "Chocolate","qty": 10}}'),
      ('{ "customer": "Jacob", "items": {"product": "Pens","qty": 2}}');

Postgres JSON Query: Query JSON Data

Now, it’s time to Query JSON Data that you just successfully inserted into PostgreSQL. To show your data like other native data types, you may use the SELECT statement as follows:

SELECT info FROM orders;

JSON Operator in WHERE clause

We can utilize the JSON operators in the WHERE clause to filter out the returning rows. For instance, if we only want results where the purchased item is Chocolate, we can write the following code:

SELECT info ->> 'customer' AS customer
FROM orders
WHERE info -> 'items' ->> 'product' = Chocolate;

To filter out the results for the records where the quantity bought is precisely 10.

SELECT info ->> 'customer' AS customer,
	info -> 'items' ->> 'product' AS product
FROM orders
WHERE CAST ( info -> 'items' ->> 'qty' AS INTEGER) = 10

Note: We have used the PostgreSQL Cast Function to convert one data type into another. 

Postgres JSON Query: JSON Operators & Functions 

In PostgreSQL, we have two native operators -> and ->> that enable us to query JSON Data. 

• The operator -> returns a JSON object field by key.
• The operator ->> produces a JSON object field by text.

As an example, if you run a SELECT query to get all the customers from your table in JSON format:

SELECT info -> 'customer' AS customer
FROM TakeOrder;

To get all the customers from your table in Text format, enter:

SELECT info ->> 'customer' AS customer
FROM TakeOrder;

Since the “->” operator returns a JSON object, you can chain it with the “->>” operator to retrieve a specific node. For instance, the following query returns all products sold:

SELECT info -> 'items' ->> 'product' as product
FROM TakeOrder
ORDER BY product;

Note: Items will first be returned as JSON objects by info -> “items.” The info->’ items’ ->>’ product’ command will generate a text list of all the products.

PostgreSQL JSON gives you a wide range of operators to manipulate your data effectively. A few more are listed below:

• #> Operator: This operation is handy when choosing an element from the main JSON object using its path. You can use the “#>” operator to access entities in the needed path, such as element names and array indexes. This operator can also be used for sequential data access.

•  #>> Operator: You can retrieve JSON data by its path using the “#>>” operator, just like you can with the “#>” operator. It cannot, however, provide you with sequential access.

Usually, by pointing to valid elements and indexes, you can construct a chain using the “->” and “#>” operators.

Note: Any of the four operators indicated above can end this chain. Remember that you must conclude the operator chain with “->>” or “#>>” if you intend to use the outcome of your chain with a function that utilizes a text data type.

Additionally, you can alter the JSON data by using aggregate functions like MIN, MAX, AVERAGE, SUM, etc.
For instance, the minimum, maximum, average, and total quantities of products sold will be returned by the following statement:

SELECT 
   MIN (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
   MAX (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
   SUM (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
   AVG (CAST (info -> 'items' ->> 'qty' AS INTEGER))
FROM TakeOrder;

• json_each function: We can convert the outermost JSON object into a set of key-value pairs using the json_each() function by writing the code:

SELECT json_each (info)
FROM orders;

• json_object_keys function: The JSON object keys() function can be used to retrieve a set of keys from a JSON object’s outermost object. The following search returns all keys from the object of nested items in the info column.

SELECT json_object_keys (info->'items')
FROM orders;

Let’s look at a few instances of JSON data being stored in PostgreSQL databases.

1) Example of storing JSON items

Make a new table first named products:

CREATE TABLE products(
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    properties JSONB
);

Three columns are present in the products table:

1. The primary key column that gives the product its unique identity is the id column.

2. The product name is kept in the name column.

3. A product’s size, color, and other attributes are stored in the properties column.

Second, add JSON information to the table of products:

INSERT INTO products(name, properties)
VALUES('Ink Fusion T-Shirt','{"color": "white", "size": ["S","M","L","XL"]}')
RETURNING *;

Output:

 id |        name        |                    properties

—-+——————–+—————————————————

  1 | Ink Fusion T-Shirt | {“size”: [“S”, “M”, “L”, “XL”], “color”: “white”}

(1 row)

Third, add many rows to the table of products:

INSERT INTO products(name, properties)
VALUES('ThreadVerse T-Shirt','{"color": "black", "size": ["S","M","L","XL"]}'),
      ('Design Dynamo T-Shirt','{"color": "blue", "size": ["S","M","L","XL"]}')
RETURNING *;

Output:

 id |        name         |                    properties

—-+———————+—————————————————

  2 | ThreadVerse T-Shirt | {“size”: [“S”, “M”, “L”, “XL”], “color”: “white”}

  3 | Design Dynamo       | {“size”: [“S”, “M”, “L”, “XL”], “color”: “blue”}

(2 rows)

Fourth, get JSON information from the table of products:

SELECT id, name, properties
FROM products;

Output:

 id |         name          |                    properties

—-+———————–+—————————————————

  1 | Ink Fusion T-Shirt    | {“size”: [“S”, “M”, “L”, “XL”], “color”: “white”}

  2 | ThreadVerse T-Shirt   | {“size”: [“S”, “M”, “L”, “XL”], “color”: “black”}

  3 | Design Dynamo T-Shirt | {“size”: [“S”, “M”, “L”, “XL”], “color”: “blue”}

(3 rows)

Fifth, obtain the product using the colors that were taken from the properties column of the JSON data:

SELECT 
  id, 
  name, 
  properties -> 'color' color 
FROM 
  products;

Output: 

 id |         name          |  color

—-+———————–+———

  1 | Ink Fusion T-Shirt    | “white”

  2 | ThreadVerse T-Shirt   | “black”

  3 | Design Dynamo T-Shirt | “blue”

(3 rows)

Visit the official documentation to learn more about Postgres JSON Functions & operators. 

How to Query a JSON Column in PostgreSQL

Getting a Particular JSON Key as Text

The following query may be used if you have a table called **events and you want to get the value linked to the key name from the JSON column params:

SELECT params->>'name' FROM events;

This will retrieve the text value from the events database for params.name.

Sorting rows according to a certain value in a JSON key

You may use the following to find all events that have a certain name, such “Click Button”:

SELECT * FROM events WHERE params->>'name' = 'Click Button';

This will retrieve every record from the events table whose value is ‘Click Button’ for the name key in the params JSON column.

Finding a component within a JSON array

The following methods can be used if your JSON column contains arrays and you wish to get the first element (index 0) of the array linked to the key ids from the params column:

SELECT params->'ids'->0 FROM events;

This will yield the events table’s params column’s first element from the ids array.

Using a nested JSON key to filter rows

Your JSON may have nested structures at times. For example, to locate users whose the nested key beta is set to true in a users table with JSON column preferences, you may use:

SELECT preferences->'beta' FROM users WHERE (preferences->>'beta')::boolean IS TRUE;

The value of preferences is initially cast using this type of query.beta converts JSON to boolean and then selects the rows that contain true values.

JSON Operator in WHERE clause

We can utilize the JSON operators in the WHERE clause to filter out the returning rows. For instance, if we only want results where the purchased item is Chocolate, we can write the following code:

SELECT info ->> 'customer' AS customer
FROM orders
WHERE info -> 'items' ->> 'product' = Chocolate;

To filter out the results for the records where the quantity bought is precisely 10.

SELECT info ->> 'customer' AS customer,
	info -> 'items' ->> 'product' AS product
FROM orders
WHERE CAST ( info -> 'items' ->> 'qty' AS INTEGER) = 10

Note: We have used the PostgreSQL Cast Function to convert one data type into another. 

Working with Postgres JSONB Query

To begin working with Postgres JSONB Query, you must first go through the steps listed below:

• Postgres JSONB Query: Create Table
• Postgres JSONB Query: Insert Data
• Postgres JSONB Query: Query JSON Data
• Postgres JSONB Query: JSON Operators & Functions

Postgres JSONB Query: Create Table

Let’s begin by creating a Table that we will use as an example to demonstrate the working of Postgres JSONB Query.

CREATE TABLE cars(
    id SERIAL PRIMARY KEY,
    cars_info JSONB NOT NULL);

Postgres JSONB Query: Insert Data

Now, successfully creating a Table in Postgres, let’s insert data into them.

INSERT INTO cars(cars_info)
VALUES('{"brand": "Toyota", "color": ["red", "black"], "price": 285000, "sold": true}'),
      ('{"brand": "Honda", "color": ["blue", "pink"], "price": 25000, "sold": false}'),
      ('{"brand": "Mitsubishi", "color": ["black", "gray"], "price": 604520, "sold": true}');

We can use the SELECT statement to view how our data in the table looks.

id | cars_info                                      
----+------------------------------------------------------------------------------------
  1 | {"sold": true, "brand": "Toyota", "color": ["red", "black"], "price": 285000}
  2 | {"sold": false, "brand": "Honda", "color": ["blue", "pink"], "price": 25000}
  3 | {"sold": true, "brand": "Mitsubishi", "color": ["black", "gray"], "price": 604520}

Postgres JSONB Query: Query JSON Data

Now let’s perform a Postgres JSONB Query to get the names of the Car Brands.

SELECT cars_info -> 'brand' AS car_name FROM cars;
  car_name  
--------------
 "Toyota"
 "Honda"
 "Mitsubishi"

PostgreSQL JSONB query using WHERE clause

Here let’s write a code to filter out the rows where the car has been sold.

SELECT * FROM cars WHERE cars_info -> 'sold' = 'true';

id | cars_info                                      
----+------------------------------------------------------------------------------------
  1 | {"sold": true, "brand": "Toyota", "color": ["red", "black"], "price": 285000}
  3 | {"sold": true, "brand": "Mitsubishi", "color": ["black", "gray"], "price": 604520}

Postgres JSONB Query: JSON Operators & Functions

• With JSONB data, a variety of built-in functions are available. Let’s have a look at a jsonb_each function query as an example:

SELECT jsonb_each('{"brand": "Toyota", "sold": "true"}'::jsonb);
    jsonb_each      
----------------------
 (sold,"""true""")
 (brand,"""Toyota""")

Here we have retrieved the rows for which the Car brand was ‘Toyota’ and the sold attribute was ‘true.’

• Let’s see a jsonb_object_keys function example:

SELECT jsonb_object_keys( '{"brand": "Mitsubishi", "sold": true}'::jsonb );
jsonb_object_keys
-------------------
 sold
 Brand

• The following example illustrates the use of the jsonb_extract_path function:

SELECT jsonb_extract_path('{"brand": "Honda", "sold": false}'::jsonb, 'brand');
jsonb_extract_path
--------------------
 "Honda"

• Let us now demonstrate an example to learn about the jsonb_pretty function:

SELECT jsonb_pretty('{"brand": "Honda", "sold": false}'::jsonb);
  jsonb_pretty    
----------------------
 { +
     "sold": false, +
     "brand": "Honda"+
 }

Before wrapping up, let’s cover some basics of JSON.

What is JSON Data?

JSON (JavaScript Object Notation) data types store JSON data. JSON is mostly used to transfer data from a server to a web application. JSON is the text that humans can read, unlike other forms. Such data may also be kept as text, but JSON data types ensure that each value is true to JSON norms.

In scenarios where requirements are changeable, representing data as JSON can be significantly more adaptable than the conventional relational data architecture. Within the same application, it is entirely conceivable for both approaches to coexist and benefit one another.

JSON is a simple, lightweight format for exchanging data that can be read by people and easily understood by computers. Two primary data structures—arrays and objects—form the foundation of JSON:

Objects

An unordered collection of key-value pairs wrapped in curly braces {} is called an object. Every pair consists of:

• a key that consists of a string with double quotations around it (“).
• The colon divides the value from the key.
• a value, which may be an object, a number, or a text.

An example of a JSON object representing a movie is as follows:

{“title”: “Chamber Italian”, “release_year”: 2006, “length”: 117}

Three keys with related values—title, release_year, and length—are included in the film object.

Arrays

An array is a set of values in order, denoted by square brackets []. The types of values don’t have to match. Furthermore, any acceptable JSON data type, including objects and arrays, may have values in an array.

An array that keeps three movie names as strings, for instance, is shown in the following: 

[“Chamber Italian”,”Grosse Wonderful”,” Airport Pollock”]

Fixing Issues in Querying JSON Columns

• Optimize Query Performance: Poorly structured JSON queries can cause performance bottlenecks. Use EXPLAIN to identify slow queries and consider adding indexes or rewriting queries for better efficiency.
• Handle Nested Structures: When dealing with nested JSON, use the appropriate operators like ->, ->>, #>, and #>> to access elements correctly. Ensure you’re chaining operators for deeper access.
• Check JSON Path Accuracy: Verify the JSON paths you’re using, especially in nested structures. A wrong path can lead to errors such as “JSON path not found.” Tools like JSON viewers can help visualize and confirm paths.
• Implement Error Handling: Missing keys in JSON data can break queries. Use functions like COALESCE or conditional logic to handle missing values gracefully, avoiding complete query failures.
• Ensure Correct Database Version: Some JSON functions are only available in newer PostgreSQL versions. Make sure you’re using a version that supports the features you need. Upgrading may be necessary.

Why store JSON Data in PostgreSQL?

The PostgreSQL database’s capacity to store & query JSON data is one of its distinctive characteristics. Previously to process JSON Data, Data Analysts and Data Engineers had to turn to specialized document storage like MongoDB. Check out this article to learn about MongoDB vs PostgreSQL.

The allure of relational databases is the ability to “save data now, sort out schema afterward.” Any data structure could be stored as plain text in databases like PostgreSQL and MySQL. Processing and speed, however, were issues since the database lacked intrinsic knowledge of the document’s schema. 

Previously, the database had to load and parse the complete text blob for each query. Furthermore, complicated regular expressions had to be used when querying deeply into the JSON record.

However, the requirement for an external document store is no longer necessary because of the robust JSON functionality included in PostgreSQL(after version 9.2).

You can also check out how to work easily with JSON Using PostgreSQL Parse JSON.

Conclusion

You can have the best of both worlds by storing & querying JSON/JSONB data in your PostgreSQL tables. Postgres JSON Query offers you the adaptability and effectiveness of a NoSQL database combined with all the advantages of a relational database. 

The article demonstrated the various operators and methods you may use while working on Postgres JSON Query. You can apply the above-discussed Queries, Functions, & Operators for JSON/JSONB data in your own PostgreSQL database by following our examples as a guide.

If you’re an avid user of PostgreSQL, copying data into a warehouse using ETL for Data Analysis might be an extensive task.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions