JSON (JavaScript Object Notation) provides a lightweight and easy-to-use data format to users who wish to transfer data over a network quickly. Developed as a JavaScript extension, JSON is replacing XML in various fields rapidly. Moreover, Developers are now improving their software’s capabilities by inserting JSON into PostgreSQL.

This blog will elaborate on the importance of inserting JSON into PostgreSQL. The article will also discuss the workings of PostgreSQL with JSON and the various PostgreSQL operators that you can deploy. Read along to understand the PostgreSQL JSON integration better!

Working with JSON Queries in PostgreSQL

The key usage of JSON lies in data transportation between a web server and a web application in a human-readable text format. To work with JSON and PostgreSQL, you must first understand the following aspects of how to insert JSON data into PostgreSQL:

Insert JSON into PostgreSQL: Create Table

This section will teach you, how to create a new table in PostgreSQL with JSON data type. In your PostgreSQL editor, write the following code:

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

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

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

Inserting JSON into PostgreSQL: Create Table

Now, once you have created a PostgreSQL Table, it’s time to insert data into the JSON column. You can use the following INSERT statement and place a new row into your TakeOrder table:

INSERT INTO TakeOrder (info)
VALUES('{ "customer": "Alex Cross", "items": {"product": "Tea","qty": 6}}');

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

INSERT INTO orders (info)
VALUES('{ "customer": "Barney Stinson", "items": {"product": "chocoloate","qty": 24}}'),
      ('{ "customer": "Ted Mosby", "items": {"product": "beer","qty": 1}}'),
      ('{ "customer": "Mary Jane", "items": {"product": "Toy car","qty": 2}}');

Inserting JSON into PostgreSQL: Querying JSON Data

Once you have completed the process of inserting JSON into PostgreSQL, it’s finally time to query that data. You can simply use the SELECT statement and display your data like other native data types:

SELECT info FROM orders;

Key PostgreSQL JSON Functions & Operators

PostgreSQL JSON provides you with multiple operators to manipulate your data efficiently. Some of the popular Operators useful for inserting JSON into PostgreSQL are: 

  • -> Operator: It enables you to select an element from your table based on its name. Moreover, you can even select an element from an array using this operator based on its index. Another important aspect of the “->” operator is that you can use it sequentially:
::json->’elementL’->’subelementM’->…->’subsubsubelementN’
  • ->> Operator: It also enables you to choose an element based on its name from the table or an array using indexes. However, you can not sequentially use this. 
  • #> Operator: This operator is useful when you wish to select an element using its path inside the main JSON object. Furthermore, you can use this operator for sequential data access. The required path may consist of entities like element names and array indexes, which you can leverage with the “#>” operator as follows:
::json#>'{elementname1,elementname2,index1,index2}’#>'{elementname3}’
  • Operator #>>: Similar to “#>” operator, you can also use the “#>>” operator to access JSON data via its path. However, it can not provide you with sequential access.

In general, you can build a chain using  “->” and “#>” operators by pointing to valid elements and indexes. This chain can end with any one of the four operators mentioned above. Keep in mind that if you want to use the result of your chain with a function that uses a text data type, you must end the operator chain with “->>” or “#>>”. 

You can understand some of the above operators better using the following examples:

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

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, chain it with the “->>” operator in case you wish 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;

The info -> ‘items’  will first return items as JSON objects. Afterwards,the info->’items’->>’product’ will return all products as text.

Using JSON operator in WHERE clause

The WHERE clause can be used in JSON operator to filter the returning rows. For example,

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

This query can find out which customer bought books from your store.

Using Aggregate Function for JSON Data

You can also apply some aggregate functions like MIN, MAX, AVERAGE, SUM, etc., to modify the JSON data. For instance, the following statement will return the minimum, maximum, average and total quantities of products sold:

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;

PostgreSQL JSON Functions

PostgreSQL also provides some built-in JSON functions that can be used to process JSON data.

json_each function

The json-each() function expands the outermost JSON object into key-value pairs. For example,

SELECT json_each (info)
FROM orders;
Inserting JSON into Postgres
Image Source

json_object_keys function

To obtain a list of keys within the outermost JSON object, you can utilize the json_object_keys() function. The following query retrieves the keys from the nested items object within the info column.

SELECT json_object_keys (info->'items')
FROM orders;
Inserting JSON into postgres
Image Source

json_typeof function

The json_typeof() function provides the data type of the outermost JSON value as a string, which can be one of the following: number, boolean, null, object, array, or string.

The following query retrieves the data type of the items.

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

The following query retrieves the data type of the quantity field of the nested items JSON object.

SELECT json_typeof (info->'items'->'qty')
FROM orders;

json_agg function

This function consolidates JSON values into a JSON array. For instance, executing the query

SELECT json_agg(my_column) FROM my_table; 

This will produce a JSON array that encompasses the values found in the “my_column” column of the “my_table” table.

jsonb_set function

This function modifies a JSON object field by assigning it a new value. For example,

UPDATE 
  my_table 
SET 
  json_column = jsonb_set(
    json_column, '{field_name}', '"new_value"'
  ) 
WHERE 
  id = 1;

json_array_elements function

This function transforms a JSON array into a set of JSON values. For example,

SELECT * from json_array_elements('[1,true, [2,false]]')

json_array_length function

This function returns the number of elements found in the outermost JSON array. For example,

json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')	

Output: 5

Importance of Inserting JSON into PostgreSQL

PostgreSQL is an ideal choice to store and process your JSON Data. Inserting JSON into PostgreSQL can prove beneficial for you because of the following reasons:

  • Developers often depend on two or more data stores such as PostgreSQL and MongoDB and then use a REST API to push the application data to various data stores. Inserting JSON into PostgreSQL will allow you to overcome the complexity of using 2 database architectures.
  • You can easily index and query your JSON data in PostgreSQL. This allows you to experience immense performance boost and scalability. Furthermore, the biggest advantage of Inserting JSON into PostgreSQL is that you can search for data and access it using SQLs statements.
  • The JSONB Data type which is an extension of JSON is one of the most sought-after options today as it ensures high-quality storage and performance. Moreover, inserting JSON into PostgreSQL enables you to perform Full-Text Searching and Indexing. performance
  • The data search performance after inserting JSON into PostgreSQL has been on-par with today’s most popular NoSQL databases including MongoDB.

Conclusion

The article listed down the importance of inserting JSON into PostgreSQL. Furthermore, it elaborated on the working of JSON PostgreSQL integration and the various operators and functions that you can use to optimize it. After reading this article, you can also try and seamlessly start inserting JSON into PostgreSQL.

Here are some essentials to understand PostgreSQL integrations:

Visit our Website to Explore Hevo

Now, to run SQL queries or perform Data Analytics on your PostgreSQL data, you first need to export this data to a Data Warehouse. This will require you to custom-code complex scripts to develop the ETL processes. Hevo Data can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 150+ multiple sources like PostgreSQL to Cloud-based Data Warehouses like Amazon Redshift, Snowflake, Google BigQuery, etc. It will provide you with a hassle-free experience and make your work life much easier by making your data analysis ready.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. Check out our pricing for a better understanding.

Share your understanding of the process of inserting JSON into PostgreSQL in the comments below!

mm
Former Research Analyst, Hevo Data

Abhinav is a data science enthusiast who loves data analysis and writing technical content. He has authored numerous articles covering a wide array of subjects in data integration and infrastructure.

No Code Data Pipeline For Your Data Warehouse