How to Insert JSON data into PostgresSQL database table

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!

What is PostgreSQL?

PostgreSQL, launched in 1996, is an open-source Relational Database Management System (RDBMS) that specializes in extensibility and SQL compliance. It was developed at the University of California(Berkeley) and debuted as the Ignes database’s replacement. PostgreSQL’s huge popularity stems from the fact that it stores data as Structured objects instead of documents. Furthermore, this free RDBMS platform operates under the standard SQL syntax and format.

PostgreSQL allows you to work on both JSON and SQL data to implement relational and non-relational queries. This way, PostgreSQL allows you to leverage SQL commands for processing data stored in tables of respective database servers. PostgreSQL uses a monolithic architecture and is written in the hardware-friendly C language. Moreover, it offers vast community support and its extensive functionalities make it one of the most popular databases in the world. This DBMS’s innovative backup mechanisms have allowed it to find applications in healthcare, banking, and manufacturing industries.

Key Features of PostgreSQL

The following features play a key role in PostgreSQL’s huge popularity: 

• PostgreSQL enables you to work with a wide range of documents and customize the data according to your needs. Moreover, its monolithic architecture operates on components that interact in an automated manner and promote data customization.
• PostgreSQL is compliant with major operating systems and uses multiple fail-safe and backup mechanisms to ensure data reliability.
• PostgreSQL provides you with variable scalability. Its ability to scale depends largely on the machine on which you are running it.
• PostgreSQL provides a robust access control system that contains special security features at row and column levels. Furthermore, this open-source tool consists of multi-factor authentication with high-standard certificates.

What is JSON?

JavaScript Object Notation (JSON) is a well-known text format used when working with structured data. It operates in a schema-less format and stores your data using key-value pairs and ordered lists. JSON, which was initially developed to act as a JavaScript derivation, is now supported by most of the popular programming languages. They even contain libraries that can get you the JSON Data Type to store your program data. In the current scenario, JSON’s primary application lies in data exchange among web servers and web clients.

JSON’s popularity has seen a rise since its inception 15 years ago and today most public Web services rely on this format for data exchange operations. JSON allows you to first convert your data into a JavaScript Object and then transfer it as a string across a network. Moreover, JavaScript also provides a global JSON Object that can enhance your JSON conversions and simplify the use of this data format.

Key Features of JSON

Some key features that make JSON a popular data format are as follows:

• Google BigQuery JSON is effective for developing JavaScript-based applications including websites, browser extensions, and much more.
• JSON format enables you to serialize and transmit structured data at a high speed over a network connection. This makes it a great choice to act as a public data source for Web services and APIs.
• The JSON format is simple to read & write. This allows it to integrate well with any modern programming language. 
• JSON leverages a lightweight interchangeable format that is completely text-based. This keeps JSON language independent and a preferred choice for developers.

To learn more about JSON, visit their official site.

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:

• Inserting JSON into PostgreSQL: Create Table
• Inserting JSON into PostgreSQL: Insert Data
• Inserting JSON into PostgreSQL: Querying JSON data

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.

1. json_each function

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

SELECT json_each (info)
FROM orders;

2. 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;

3. 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;

4. 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.

5. 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;

6. 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]]')

7. 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:

• HubSpot to PostgreSQL Migration: 2 Easy Methods
• Connecting PostgreSQL to Power BI: Unleash Insights
• Migrate MySQL to PostgreSQL: 4 Easy Methods

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.

Frequently Asked Questions