Overview

In this article, we will learn about JSON datatypes, functions, operators in PostgreSQL and how to use them in practical world. We’ll also go into the basics of storing and retrieving JSON data in PostgreSQL and how to make them readily accessible.

Prerequisite

A basic understanding of JSON and PostgreSQL databases.

What is JSON?

Before we move ahead in this tutorial, let us quickly go over the concept of JSON and its importance in programming world. JSON stands for JavaScript Object Notation. It is a common way to store and exchange data, especially in web applications. JSON is stored in key-value pair. Keys and values are separated by a colon and every key/value pair is separated by a comma. Strings must be double quoted.

Sample JSON object:

{"name":"Raisina", "age":19, "gender":F}

This JSON object has 3 properties: name, age and gender. Each property has a value (Raisina, 19, and F).

Need of JSON in PostgreSQL

Having understood the basic concept of JSON, we must understand why JSON is needed in PostgreSQL and its relevance. In the world of web applications, we often come across a need to interact with other API's or services which means the data is exchanged in some format. This exchange of data is called request and response, and the most commonly used format is JSON. Now, if we need to store these request and responses in some table for auditing purpose, we must design the table in such a way that it can accept JSON data. Therefore, the answer to this situation is JSON datatypes which we will study in the next section.

JSON datatypes in PostgreSQL

PostgreSQL supports two types of JSON datatypes, namely json and jsonb. Json and jsonb accept almost identical sets of values as input and the major difference between the two is efficiency. While the json type stores an exact copy of the input text, which processing functions must reparse on each execution, the jsonb data is stored in decomposed binary format that needs no reparsing and is therefore processed faster. Indexing is supported by jsonb thus adding to its advantage.

Create a table with the JSONB data type

It is possible to create a table and give a column a data type of JSON or JSONB just like a column of data type of Int, Varchar or Double.

Example 1: valid JSON data

CREATE TABLE employeeProfile (
  id Int NOT NULL PRIMARY KEY, 
  employee_info JSONB
);

Next task is to insert data in the table, the important point to remember here is the data must be in a valid JSON format.

INSERT INTO employeeProfile (id, employee_info) 
VALUES 
  (
   1, '{"name": "John Grogan", "Department": "Automobile", "Salary": "10000USD"}'
  )

Example 2: invalid JSON data

Let us also look at an example with invalid JSON data. In this case, we haven't added quotes around the name value.

INSERT INTO employeeProfile (id, employee_info) 
VALUES 
  (
   4, '{"name": John Grogan, "Department": "Automobile", "Salary": "10000USD"}'
  )

Example 3: query JSON data

Let us now query the table to verify the inserted records. We can verify the data from psql terminal or from PgAdmin. PgAdmin presents a concise view of the table and helps to understand the return datatype.

JSON operators

Using the following operators one can store, manipulate, and query data in JSON format in PostgreSQL.

Operator 1: A hyphen followed by a right angle bracket (->)

This operator is used to extract a specific value from a JSON object, by specifying the key as a “child” to the “parent”. This operator extracts the field name, with the quote around it.

Example:

SELECT 
  id, 
  employee_info -> 'name' as name 
FROM 
  employeeprofile;

Operator 2: A hyphen followed by couple of right angle brackets (->>)  

Description: This operator helps to extract a JSON object field as text without the quotes around it from a JSON object.

Example:

SELECT 
  id, 
  employee_info ->> 'name' as name 
FROM 
  employeeprofile;

JSON Functions

Let us now take a look at some of the functions available in PostgreSQL that helps to manipulate JSON data.

JSON_AGG()

Description: The json_agg function aggregates record values for the given key as one JSON object. Let us study the following queries to understand how this works.

Example:

SELECT json_agg(employee_info -> 'name') FROM employeeprofile;

The 1st snapshot presents a view of all data present in the table but our goal here is to get only the names from all the records  as one JSON object. The function json_agg() helps to achieve this cause by aggregating all data pertaining to the key 'name' into one JSON object as shown in the second snapshot.

JSONB_SET()

Description: The jsonb_set function is used to update a JSON object field with a new value. Let us look at an example to understand the behaviour.

Basic Syntax:

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

Example:

Data before update

Performing update:

UPDATE 
  employeeprofile 
SET 
  employee_info = jsonb_set(
    employee_info, '{name}', '"Ashwin Haq"'
  ) 
WHERE 
  id = 2;

Data after update:

Thus we see here that the data for ID 2 was updated successfully.

Conclusion

In conclusion, PostgreSQL support for JSON provides developers with the ability to simplify data models, enhance application performance, and so much more. I hope this article with help you get started on your journey with JSON in PostgreSQL.