How to parse JSON in PostgreSQL

Data Parsing is a crucial step in understanding data and creating meaningful insights. It is a simple process of transforming data from one format to another to make it more readable and subsequently ready for analysis.

Since the release of PostgreSQL version 9.2, PostgreSQL has introduced a number of JSON operators and functions to parse JSON data. PostgreSQL parse JSON operation helps you and your workgroup to break JavaScript objects like arrays and objects into smaller pieces of data, resulting in improved Data Accessibility for everyone who interacts with your PostgreSQL database.

In this Parse JSON PostgreSQL guide, we discuss the basics of parsing, what it is and how it helps to make sense of information encoded in your JSON files. You’ll learn about the different types of PostgreSQL Parse JSON operators and functions that allow you to parse your JSON files and make them more accessible for your people than before.

Table of Contents

• What Is PostgreSQL?
  • Key Features of PostgreSQL
• What Is Data Parsing?
• What Is JSON?
• Why is PostgreSQL Parse JSON Required?
• How Does PostgreSQL JSON Parse Work?
  • PostgreSQL Parse JSON Operators
  • PostgreSQL Parse JSON Functions
• Key PostgreSQL Parse JSON Operation In Action
  • Inserting PostgreSQL JSON Data
  • Querying PostgreSQL JSON Data
  • Postgres JSON Parse Operation
• Conclusion

What Is PostgreSQL?

PostgreSQL is a Relational Database Management System (RDBMS) developed by the PostgreSQL Global Development Group. It has been in use for over 20 years and supports both SQL and JSON for relational and non-relational queries in order to provide flexibility and SQL compliance.

PostgreSQL has had a reputation for being a dependable, feature-rich, and performance-rich utility from its beginnings. Many businesses rely on PostgreSQL as their primary data storage/data warehouse for online, mobile, geospatial, and analytics applications. 

PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. 

Key Features of PostgreSQL

• Open-Source: PostgreSQL is an Object-Relational Database Management System (ORDBMS). This allows PostgreSQL to provide both Object-Oriented and Relational Database functionality. It is a free-to-use Open-Source ORDBMS.
• Prominent User Base: PostgreSQL users include prominent names like Apple, Cisco, Etsy, Facebook, Instagram, Red Hat, Skype, Spotify, and Yahoo, to name a few.
• Multiversion Concurrency Control: To manage concurrent requests, PostgreSQL features a multi-version concurrency control which gives each transaction a “snapshot” of the database, allowing changes to be made without affecting other transactions. This largely eliminates the need for read locks, and ensures the database maintains ACID principles. 
• Reliability and Standards Compliance: PostgreSQL’s write-ahead logging makes it a fault-tolerant database. Its large base of open source contributors lends it a built-in community support network. PostgreSQL is ACID compliant, and has full support for foreign keys, joins, views, triggers, and stored procedures, in many different languages. 
• A Supportive Community: PostgreSQL offers a dedicated community that is always available to you. Private, third-party support services are also available. The community updates the PostgreSQL platform via the PostgreSQL Global Development Group.

For more information on PostgreSQL Master-Slave Replication, you can visit our extensive guide covering broad aspects here: Achieving PostgreSQL Master Slave Replication: 7 Easy Steps. You can also learn how to use the PostgreSQL command line for managing your PostgreSQL database in this guide: How to Use PostgreSQL Command line to Manage Databases? | Made Easy.

What Is Data Parsing?

In essence, Parsing is a process of dividing a whole into smaller logical parts for better examination. In the domain of the Data Industry, Data Parsing is the process of transforming data from one format to another, generally into one that is easily comprehensible. 

Data Parsing can mean anything from simply breaking up the data to a full analysis of data structures and their organization using Natural Language Processing (NLP) algorithms. In some cases, Data Parsing is essential for converting unstructured or unreadable data into well-structured and easily readable data for better Data Analysis.

Data Parsing is used by web developers to make computer codes simpler. Likewise, data analysts also use SQL Data Parsing to parse and execute a SQL query step-by-step and then show the results. Data parsing is also used for competitive analysis, as part of equity research or business valuation, by investors and data analysts in conjunction with web scraping tools.

You can either build or buy a Data Parser to convert data into readable data. Typically, most Data Parsers work in three steps. They:

• Step 1: Gain access to or extract data from data sources
• Step 2: Load it into the parsing engine
• Step 3: Process it to extract meaningful information

After conversion, this data can then be shared across business teams and clients to make your business operations agile and scalable.

In the upcoming sections, we discuss PostgreSQL Parse JSON, one of the many ways of Data Parsing to convert JSON files into accessible formats.

What Is JSON?

JSON or JavaScript Object Notation is an open standard file format (.json) for structuring, storing, and transmitting data between servers and web applications. It uses a simple format to store data in the form of “key:value” pairs and is readily comprehensible to humans as well. Matter of fact, JSON is also easy to parse.

Here’s a simple example of a JSON file containing an array called groceries with three objects type, name and price.

{
	"groceries": [

		{
			"type": "fruit",
			"name": "apple",
			"price": 1.36
		},
		{
			"type": "vegetable",
			"name": "lettuce",
			"price": 0.32
		},
		{
			"type": "vegetable",
			"name": "bitter gourd",
			"price": 0.79
		}
	]
}

JSON supports both simple and complex data types like number, string, boolean, null, array, value, and object.

The best part about using JSON is that it is lightweight and language-independent. JSON also has in-built support for almost all the front-line languages/frameworks like C, C++, C#, Java, JavaScript, Perl, and Python.

Why is PostgreSQL Parse JSON Required?

To understand the importance of PostgreSQL Parse JSON operation, consider a usual scenario. Let’s say you asked your sales team for data on recent customers who had purchased a product from your website. 

They agreed to give you the details, but you received something unexpected like this.

{
"customers": [
{
  	"id": 1,
  	"first_name": "Dayna",
  "last_name": "Wile",
  "gender": "Female",
  "email": "dwile0@facebook.com",
  "phone": "989-311-5262"
}, {
  "id": 2,
  "first_name": "Bliss",
  "last_name": "Pogosian",
  "gender": "Female",
  "email": "bpogosian1@nytimes.com",
  "phone": "466-731-8534"
}, {
  "id": 3,
  "first_name": "Lalo",
  "last_name": "Dener",
  "gender": "Male",
  "email": "ldener2@amazon.co.jp",
  "phone": "656-432-6184"
}, {
  "id": 4,
  "first_name": "Gayleen",
  "last_name": "Mateescu",
  "gender": "Female",
  "email": "gmateescu3@hhs.gov",
  "phone": "281-359-3811"
}, {
  "id": 5,
  "first_name": "Tadd",
  "last_name": "Lotherington",
  "gender": "Male",
  "email": "tlotherington4@nih.gov",
  "phone": "541-923-6204"
}
]
}

Clearly, this format isn’t suitable for storage. To get these records in your relational database like PostgreSQL, you need PostgreSQL Parse JSON operation. 

PostgreSQL Parse JSON operation will take your JSON key-value pairs and convert them into a format that is compatible with PostgreSQL’s table structure. You can parse the JSON objects using PostgreSQL’s JSON operators and functions, and then insert the values into your desired PostgreSQL tables for future use.

Next up, we discuss the process of performing parse JSON Postgres operation.

How Does PostgreSQL JSON Parse Work?

Since the release of PostgreSQL 9.2, there have been several significant enhancements, including support for JSON data types. PostgreSQL now provides various functions and operators for users to work with JSON data. 

PostgreSQL Parse JSON Operators

Some commonly used JSON operators available for use in PostgreSQL are listed below:

Operator	Description	Example
json -> integer → json	Extracts nth element of JSON array (array elements are indexed from zero, and negative integers are counted from the end)	‘[{  “id”: 1,  “first_name”: “Walther”,  “last_name”: “Laybourn”}, {  “id”: 2,  “first_name”: “Calley”,  “last_name”: “Bamblett”}, {  “id”: 3,  “first_name”: “Cecelia”,  “last_name”: “Asquez”}]’::json -> 2 -> {  “id”: 2,  “first_name”: “Calley”,  “last_name”: “Bamblett”}
json ->text → json	Extracts JSON object field with the given key	‘{“Name”: {“Calley”:”Bamblett”}}’::json -> ‘Name’ → {“Calley”:”Bamblett”}
json ->> integer → text	Extracts nth element of JSON array, as text	‘[10,20,30]’::json ->> 2 → 30
json ->> text → text	Extracts JSON object field with the given key, as text	‘{“Walther”:1,”Laybourn”:2}’::json ->> ‘Laybourn’ → 2
json #> text[] → json	Extracts JSON sub-object at the specified path, where path elements can be either field keys or array indexes	‘{“Customer”: {“FullName”: [“Walther”,”Laybourn”]}}’::json #> ‘{Customer,FullName,1}’ → “Laybourn”
json #>> text[] → text	Extracts JSON sub-object at the specified path as text	‘{“Customer”: {“FullName”: [“Walther”,”Laybourn”]}}’::json #>> ‘{Customer,FullName,1}’ → Laybourn

Key PostgreSQL Parse JSON Functions

The table below shows JSON functions that are available for use in PostgreSQL.

Function	Description	Example
to_json ( anyelement ) → json	Converts SQL value(s) to JSON. Any SQL arrays or composites if provided will be converted to JSON arrays and objects	to_json(‘Walther Laybourn says “Hi.”‘::text) → “Walther Laybourn says “Hi.””
array_to_json ( anyarray [, boolean ] ) → json	Transforms a SQL array to a JSON array	array_to_json(‘{{22,24},{87,89}}’::int[]) → [[22,24],87,89]]
row_to_json ( record [, boolean ] ) → json	Transforms a SQL composite value to a JSON object	row_to_json(row(1,’Walther’)) → {“f1″:1,”f2″:”Walther”}
json_object ( text[] ) → json	Creates a JSON object out of a text array.	json_object(‘{“FirstName”, “Walther”, “SecondName”, “Laybourn”}’) → {“FirstName” : “Walther”, “SecondName” : “Laybourn”}
json_object ( keys text[], values text[] ) → json	This JSON function takes keys and values pairwise from separate text arrays.	json_object(‘{FirstName,Walther}’, ‘{SecondName,Laybourn}’) → {“FirstName”: “Walther”, “SecondName”: “Laybourn”}
json_array_elements ( json ) → set of json	Expands the top-level JSON array into a smaller set of JSON values	select * from json_array_elements(‘[customers,1, [FirstName, Walther]]’) →    value———– customers 1 [FirstName,Walther]
json_array_length ( json ) → integer	Returns the number of elements in a JSON array as an integer.	json_array_length(‘[1,2,3,{“s1″:1,”s2”:[5,6]},{s3:[6,7,8]},4]’) → 6
json_each ( json ) → setof record ( key text, value json )	Expands the top-level JSON object into a set of key/value pairs.	select * from json_each(‘{“FirstName:”Walther”, “LastName”:”Laybourn”}’) →  key             |  value————-+——- FirstName  | “Walther” LastName  | “Laybourn”
json_object_keys ( json ) → setof text	Returns the set of keys in the top-level JSON object.	select * from json_object_keys(‘{“p1″:”oranges”,”p2″:{“p3″:”apples”}’) → json_object_keys—————— p1 p2

PostgreSQL supports many more JSON functions and operators than the ones listed here. You can find more information about these in the following PostgreSQL documentation – PostgreSQL JSON Functions and Operators.

PostgreSQL Parse JSON Operation In Action

Since you now have all the knowledge about frequently used PostgreSQL parse JSON functions and operators, let’s unravel our previous problem and see how we can parse our customer’s JSON file we received from the Sales team.

1) Inserting PostgreSQL JSON Data

After receiving the customer’s JSON file, you can use the INSERT function to insert JSON data into your PostgreSQL tables. 

Here’s the command to do so:

INSERT INTO
  customers (customer_json_info)
VALUES(
    '{ "id": 1, "first_name": "Dayna", "last_name": "Wile", "gender": "Female", "email", "dwile0@facebook.com", "phone": "989-311-5262"}'
  ),
  (
    '{ "id": 2, "first_name": "Bliss", "last_name": "Pogosian", "gender": "Female", "email": "bpogosian1@nytimes.com", "phone": "466-731-8534"}'
  ),
  (
    '{ "id": 3, "first_name": "Lalo", "last_name": "Dener", "gender": "Male", "email": "ldener2@amazon.co.jp", "phone": "656-432-6184"}'
  ),
  (
    '{ "id": 4, "first_name": "Gayleen", "last_name": "Mateescu", "gender": "Female", "email": "gmateescu3@hhs.gov", "phone": "281-359-3811"}'
  ),
  (
    '{ "id": 5, "first_name": "Tadd", "last_name": "Lotherington", "gender": "Male", "email": "tlotherington4@nih.gov", "phone": "541-923-6204"}'
  );

Once it’s in the database, your PostgreSQL customer_json_info column would look like this:

customer_json_info

{ “id”: 1, “first_name”: “Dayna”, “last_name”: “Wile”, “gender”: “Female”, “email”, “dwile0@facebook.com”, “phone”: “989-311-5262”}

{ “id”: 2, “first_name”: “Bliss”, “last_name”: “Pogosian”, “gender”: “Female”, “email”: “bpogosian1@nytimes.com”, “phone”: “466-731-8534”}

{ “id”: 3, “first_name”: “Lalo”, “last_name”: “Dener”, “gender”: “Male”, “email”: “ldener2@amazon.co.jp”, “phone”: “656-432-6184”}

{ “id”: 4, “first_name”: “Gayleen”, “last_name”: “Mateescu”, “gender”: “Female”, “email”: “gmateescu3@hhs.gov”, “phone”: “281-359-3811”}

‘{ “id”: 5, “first_name”: “Tadd”, “last_name”: “Lotherington”, “gender”: “Male”, “email”: “tlotherington4@nih.gov”, “phone”: “541-923-6204”}

2) Querying PostgreSQL JSON Data

To query data, you can use a SELECT statement like in any other SQL query. You can use the native PostgreSQL operators to query the data in PostgreSQL.

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

As an example, if you run a SELECT query,

SELECT customer_json_info ->> CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

You will receive the following output.

full_name

Dayna Wile

Bliss Pogosian

Lalo Dener

Gayleen Mateescu

Tadd Lotherington

You can also run another SELECT query to expand the top-level JSON object into a set of key/value pairs. 

Let’s consider a simple table of our first two customers; Dayna Wile who purchased a T-shirt from our online store, and Bliss Pogosian who purchased trousers. 

---------------------------------------------------------------------
| id | first_name | last_name | product_description                 |                  
----- ------------ ----------- ---------------------------------------
| 1  | Dayna      | Wile      | {"category":"apparel","product":tshirt"}                    |
----------------------------------------------------------------------
| 2  | Bliss      | Pogosian  | {"category":"accessories","product":"cap"}                  |
----------------------------------------------------------------------

You can return a set of rows with key and value column names using the json_each_text PostgreSQL parse JSON function. 

SELECT * FROM customers, 
json_each_text(customers.product_description) AS json_data
WHERE VALUE = 'apparel';

Output:

| id | first_name | last_name | product_description| key | value |    
 ---- ------------ ----------- -------------------- ----- ------- 
| 1  | Dayna      | Wil       | {"category":"apparel","product":"tshirt"} | category | apparel | -------------------------------------------------------------------

3) Postgres JSON Parse Operation

Here’s another simple example to parse PostgreSQL JSON fields. You can write an SQL query to output an array of all customer_note keys:

CREATE TABLE customers (
    id serial primary key,
    customers_json_2 json
);

INSERT INTO customers (customers_json_2) VALUES (
  '{"customer_note": {"note_199": {"message_number": 1453197190, "status": 1}, "note_215": {"message_number": 1459325611, "status": 1}, "note_219": {"message_number": 1454244074, "status": 1}, "note_225": {"message_number": 1453981312, "status": 1}, "note_229": {"date": 1459327685, "status": 1}}}'::json
);

When you run the SELECT command with json_object_keys PostgreSQL parse JSON operation,

SELECT json_object_keys(customers_json_2->'customer_note') FROM customers

You get the following output.

json_object_keys
note_199
note_215
note_219
note_225
note_229

Here’s another query to return the type of the top-level JSON value.

SELECT json_typeof('{ "id": 1, "first_name": "Dayna", "last_name": "Wile", "gender": "Female", "email", "dwile0@facebook.com", "phone": "989-311-5262"}');

 json_typeof
---------------
 object
(1 row)

There are many more interesting functions that operate on JSON like  jsonb_path_query,  json_to_record, and jsonb_insert  that can be used to perform parse JSON Postgre operations. In this guide, we focused on some basic examples, but the same fundamental concepts allow you to parse complex JSON files in your PostgreSQL tables. 

Conclusion

PostgreSQL offers a number of ways to perform a parse JSON PostgreSQL operation by using different PostgreSQL parse JSON operators and functions. In this guide, we discussed a select few functions to help you understand how parsing or breaking of JSON objects can be performed for a better understanding of data in your PostgreSQL tables. 

If you are a PostgreSQL user, replicating data into a warehouse using ETL for Data Analysis can get demanding. For starters, this problem is exaggerated by the fact that they need extensive money and resources to hire data engineers and analysts to make sense of this data. 

Luckily, you can set up and start Data Replication from PostgreSQL to your favorite warehouse in a matter of minutes using Hevo. Even non-data professionals can set up and run their PostgreSQL Data Pipelines without any extensive training or technical resources. 

Hevo Data is an effective and simple solution for replicating and integrating data from heterogeneous Data Sources into a warehouse, therefore allowing small and medium businesses to operate smoothly with their everyday applications.

Hevo Data with its strong integration with 100+ Data Sources such as PostgreSQL, MySQL, and MS SQL Server, allows you to not only export data from sources & load data to the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools. 

Visit our Website to Explore Hevo

Hevo lets you replicate your data from your PostgreSQL database to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt within minutes with just a few clicks.

Give Hevo a try. Sign Up here for a 14-day full feature access trial and experience the feature-rich Hevo suite first hand. You can also check our pricing and make a decision on your best-suited plan. 

Share your thoughts on learning about PostgreSQL Parse JSON operation in the comments section below. If you have any questions, do let us know. We’d be happy to help.