How to Manage JSON Data with PostgreSQL

Want to know the way to manage JSON data with PostgreSQL? Read this article to have a full understanding of this issue. I believe that most people don’t use it correctly, and it causes problems and unhappiness in the long run. So, first of all, we will explain PostgreSQL and JSON data briefly and then go on with the rest of this article.

Brief Explanation of PostgreSQL JSON Data Type

We have another data type in PostgreSQL, which is called JSON data and stands for Javascript Object Notation. JSON data is a user-friendly data format for users who are willing to transfer data in a network at high-speed. JSON replaces XML in various fields rapidly. Also, nowadays, developers enhance their software capability by pairing their application’s JSON data with PostgreSQL to reach a query processing environment.

In other words, the main purpose and role of JSON is transporting data between a server and a web application. JSON has a human-readable format. Since, version 9.2, PostgreSQL supports native JSON data and provides a lot of functions and operators to control JSON data.

Manage JSON Data With PostgreSQL

Now that you have a clear understanding of this issue, let’s go further and learn the way to manage JSON data step by step with PostgreSQL. We suggest that you visit the Linux VPS server plans provided on our website before starting the process of Managing JSON Data with PostgreSQL.

How to Create a New Table to Practice JSON Data Type

– In the first step, let’s create a new table to practice JSON data type:

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

As you see above, our orders table includes 2 columns:

1- The id column is the primary key column that identifies the order.

2- The info column saves the data in the form of JSON data type.

How to Insert JSON Data after Creating a Table

– The next step is to insert JSON data after creating a table.

Now that you have created a PostgreSQL table, to insert data, you have to make sure that data is in a valid format. So, apply the following Insert statement and then put a new row into your order table, as we do here:

INSERT INTO orders (info)
VALUES('{ "customer": "Terri Person", "items": {"product": "coffee","qty": 6}}');

The above command represents that our customer, Terri Person bought 6 cups of coffee. Now, use the below command to insert multiple rows at the same time in your table:

INSERT INTO orders (info)
VALUES('{ "customer": "Linda Evanty", "items": {"product": "Toy Car","qty": 24}}'),
('{ "customer": "Will Path", "items": {"product": "Teddy bear","qty": 1}}'),
('{ "customer": "Jannson Miller", "items": {"product": "Doll","qty": 2}}');

Querying JSON Data

– The last step is to query JSON data. Finally, if you want to query the JSON data, you should use the select statement and present your data in a manner similar to other native data types. So, use the below command:

SELECT info FROM orders;

Now, you will see that PostgreSQL converts a result set in the form of JSON.

Key Operators of PostgreSQL to Get the JSON Data

PostgreSQL JSON provides several operators to manipulate your data in a useful way. Some of the operators that are useful for inserting JSON data in PostgreSQL are as follows:

-> operator: This operator helps to return the JSON object field by key.

->> operator: This operator helps to return the JSON object field by text.

e.g: In the following query, the -> operator is used to get all customers in the form of JSON:

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

e.g For this query, the ->> operator is used to get all customers in the form of text:

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

At this point, you can chain operator -> with operator ->> to retrieve a specific node.

e.g, the following statement returns all products sold:

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

As the last part, use the JSON operator in the Where clause:

To filter the returning rows, you can use JSON operators in the WHERE clause.

For example, to figure out who bought the Toy Car, you should use the following query:

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

If you want to know who bought 2 products at the same time, you should use the query below:

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

Conclusion

Well, Done! In this article, we tried to show how you can manage JSON data with PostgreSQL, and you learned how to use operators to process JSON data efficiently. Now, you can easily use this data and manage it.