PostgreSQL, one of the most advanced open-source relational database systems, offers rich support for JSON (JavaScript Object Notation) data types. This feature allows developers to seamlessly store, query, and manipulate JSON data within a relational database. This article delves into PostgreSQL’s JSON data types, their capabilities, and best practices for effectively using JSON in a database environment.

Introduction to JSON in PostgreSQL

JSON is a lightweight data-interchange format widely used in modern web development. It is human-readable, easy to parse, and is often used to transmit data between servers and web applications. PostgreSQL introduced native support for JSON with version 9.2, and with subsequent releases, the capabilities were expanded significantly to enable powerful querying and manipulation of JSON data.Introduction to JSON in PostgreSQL

Lets play with an example

1). Create a table

create table myemp
(
   id int PRIMARY KEY,
   name VARCHAR(100),
   jdata JSON
)

2). Insert values to the table

insert into myemp values (1,'Vipin','{"age":35,"gender":"male", "mark":{"english":85}}');
insert into myemp values (2,'Rahul','{"age":42,"gender":"male", "mark":{"english":65}}');
insert into myemp values (3,'Diya','{"age":28,"gender":"female", "mark":{"english":56,"hindi":82}}');

3). Simple Fetch

select * from myemp;

output

Now so far its very basics and clear. Now lets try doing for additional activities on the JSON data. Once you have JSON data stored in your PostgreSQL database, you can query specific fields using operators like ->, ->>

Extracting Fields

• The -> operator extracts a JSON object or an array element.
• The ->> operator extracts the value as text.

4). Fetch a specific property of the JSON

select jdata->'age' as age from myemp

output

4). Fetch with a filter

select * from myemp
where (jdata->>'age')::numeric>30

output

Extracting Nested JSON Fields

JSON documents can contain nested structures. You can extract values from nested objects using -> or ->>. Suppose the details field contains a nested object with a specs field, and you want to extract a specific property from that nested object:

5). Fetch the ‘hindi’ property in the ‘mark’ property

select jdata->'mark'->>'hindi' as hindi_mark from myemp

output

6). Filter it based on the property existence

SELECT * 
FROM myemp
WHERE (jdata->'mark')::JSONB ? 'hindi';

output

JSON & JSONB

PostgreSQL provides two primary JSON data types:

• JSON: This stores JSON data in a text format and ensures that it is valid JSON upon insertion.
• JSONB: This stands for “Binary JSON.” It stores JSON in a decomposed binary format, allowing for faster processing and querying.

Key Differences Between JSON and JSONB

Storage:

• JSON: Stored as plain text. When querying, the data must be parsed, which can be slow for large datasets.
• JSONB: Stored in a binary format, enabling faster searches and manipulation.

Processing:

• JSON: Parsing occurs each time the data is queried, which can slow down read operations.
• JSONB: Data is parsed once upon insertion, leading to better performance when querying.

Order of Keys:

• JSON: Retains the original key order.
• JSONB: Does not retain key order and may reorder them for optimization.

Duplicates:

• JSON: Allows key duplication within the same object (though this is generally bad practice).
• JSONB: Does not allow duplicate keys; the last one will overwrite the previous ones.

In most cases, JSONB is preferred for its performance benefits, especially when frequent querying is required. JSON might be used if you need to preserve key ordering or store raw JSON as-is.

Thanks for reading my article. I hope you enjoyed or gained some new insights and I believe it was useful.

Enjoy programming 🙂