Sometimes, database records contain information that is not easily represented using traditional columns.
For example, a university student may have additional information such as:
We can store this information using PostgreSQL’s JSON and JSONB data types.
JSON allows us to store structured data in a format similar to JavaScript objects.
For example:
{
"name": "Ali",
"age": 22,
"department": "Computer Science"
}
This structure contains three properties:
nameagedepartmentJSON stands for JavaScript Object Notation.
It is a lightweight format used to store and exchange structured data.
JSON stores information using key-value pairs.
Example:
{
"student_id": 101,
"student_name": "Ali",
"age": 22,
"department": "Computer Science"
}
In this example:
| Key | Value |
|---|---|
| student_id | 101 |
| student_name | Ali |
| age | 22 |
| department | Computer Science |
Important: JSON is not limited to JavaScript. PostgreSQL, Python, PHP, Java, and many other technologies support JSON.
PostgreSQL provides two data types for storing JSON information:
JSONJSONBBoth store JSON-formatted data, but they work differently.
The JSON data type stores an exact copy of the input JSON text.
It preserves:
The JSONB data type stores JSON data in a decomposed binary format.
It provides:
Comparison:
| Feature | JSON | JSONB |
|---|---|---|
| Storage | Original JSON text | Binary representation |
| Formatting | Preserved | Not preserved |
| Key order | Preserved | Not preserved |
| Duplicate keys | Preserved in input text | Only the last value is retained |
| Searching | Less efficient | Generally more efficient |
| Indexing | Limited | Supports GIN indexes |
| Common use | Preserving JSON text | Querying and processing JSON data |
Recommendation: Use JSONB for most applications where you need to search, filter, or process JSON data.
Let’s create a table named students_json.
CREATE TABLE students_json (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
contact_info JSON,
additional_info JSONB
);
Explanation:
student_id: Stores the student’s ID.student_name: Stores the student’s name.contact_info: Stores JSON data.additional_info: Stores JSONB data.We can insert JSON data using the INSERT INTO statement.
INSERT INTO students_json
VALUES (
1,
'Ali',
'{"email": "ali@example.com", "phone": "03001234567"}',
'{"age": 22, "department": "Computer Science"}'
);
Insert another record:
INSERT INTO students_json
VALUES (
2,
'Sara',
'{"email": "sara@example.com", "phone": "03007654321"}',
'{"age": 21, "department": "Information Technology"}'
);
Display all records:
SELECT * FROM students_json;
-> OperatorThe -> operator retrieves a JSON object field as JSON.
Syntax:
column_name -> 'key'
Example:
SELECT
student_name,
additional_info -> 'department' AS department
FROM students_json;
This returns the department as a JSON value.
For example:
"Computer Science"
Notice that the value includes quotation marks because it is returned as JSON.
->> OperatorThe ->> operator retrieves a JSON object field as text.
Syntax:
column_name ->> 'key'
Example:
SELECT
student_name,
additional_info ->> 'department' AS department
FROM students_json;
Output:
| student_name | department |
|---|---|
| Ali | Computer Science |
| Sara | Information Technology |
Difference between -> and ->>:
| Operator | Returns |
|---|---|
-> |
JSON |
->> |
Text |
This is an important difference when working with JSON data.
JSON objects can contain other JSON objects.
Example:
{
"personal": {
"age": 22,
"city": "Dera Ghazi Khan"
},
"academic": {
"department": "Computer Science"
}
}
Let’s insert this information:
INSERT INTO students_json
VALUES (
3,
'Ahmed',
'{"email": "ahmed@example.com"}',
'{
"personal": {
"age": 23,
"city": "Dera Ghazi Khan"
},
"academic": {
"department": "Computer Science"
}
}'
);
Access the nested department:
SELECT
student_name,
additional_info -> 'academic' ->> 'department' AS department
FROM students_json;
Access the nested city:
SELECT
student_name,
additional_info -> 'personal' ->> 'city' AS city
FROM students_json;
Explanation:
-> 'personal' accesses the personal object.->> 'city' retrieves the city as text.JSON also supports arrays.
Example:
{
"skills": ["C++", "Python", "SQL"]
}
We can access array elements using their index.
Important: JSON array indexing starts from 0.
| Index | Value |
|---|---|
| 0 | C++ |
| 1 | Python |
| 2 | SQL |
Insert a record:
INSERT INTO students_json
VALUES (
4,
'Hina',
'{"email": "hina@example.com"}',
'{"skills": ["C++", "Python", "SQL"]}'
);
Access the first skill:
SELECT
student_name,
additional_info -> 'skills' ->> 0 AS first_skill
FROM students_json;
Output:
| student_name | first_skill |
|---|---|
| Hina | C++ |
Access the second skill:
SELECT additional_info -> 'skills' ->> 1
FROM students_json
WHERE student_id = 4;
Output:
Python
The ? operator checks whether a JSONB object contains a specified top-level key.
It returns TRUE or FALSE.
Example:
SELECT
student_name,
additional_info ? 'age' AS has_age
FROM students_json;
For the additional_info column, PostgreSQL checks whether the key age exists.
Another example:
SELECT *
FROM students_json
WHERE additional_info ? 'skills';
This query returns records containing a top-level skills key.
Note: The ? operator also checks whether a string exists as an array element when the JSONB value is an array.
@>The @> operator checks whether one JSONB value contains another JSONB value.
Example:
SELECT *
FROM students_json
WHERE additional_info @> '{"department": "Computer Science"}';
This query finds records whose additional_info contains the specified department property.
For nested data:
SELECT *
FROM students_json
WHERE additional_info @> '{"academic": {"department": "Computer Science"}}';
The query checks for the nested object and its department value.
We can update a JSONB value using the jsonb_set() function.
Syntax:
jsonb_set(target, path, new_value)
Example: Update Ali’s age.
UPDATE students_json
SET additional_info = jsonb_set(
additional_info,
'{age}',
'23'
)
WHERE student_id = 1;
Here:
additional_info is the JSONB column.'{age}' specifies the key to update.'23' is the new JSON value.Check the updated record:
SELECT *
FROM students_json
WHERE student_id = 1;
Update Ahmed’s city:
UPDATE students_json
SET additional_info = jsonb_set(
additional_info,
'{personal,city}',
'"Multan"'
)
WHERE student_id = 3;
Notice that "Multan" is enclosed in double quotation marks inside the SQL string because it is a JSON string value.
We can use the || operator to combine JSONB objects.
This can be used to add a new key.
Example:
UPDATE students_json
SET additional_info = additional_info ||
'{"graduation_year": 2027}'::jsonb
WHERE student_id = 1;
Now Ali’s JSONB data includes the graduation_year key.
Check the result:
SELECT additional_info
FROM students_json
WHERE student_id = 1;
Important: If the key already exists, the new value replaces the old value.
The - operator removes a key from a JSONB object.
Example:
UPDATE students_json
SET additional_info = additional_info - 'age'
WHERE student_id = 1;
This removes the top-level age key.
To remove a nested key, use the #- operator:
UPDATE students_json
SET additional_info = additional_info #- '{personal,city}'
WHERE student_id = 3;
This removes the nested city key from the personal object.
We can use the ::text cast to convert JSON or JSONB data into text.
Example:
SELECT
student_name,
additional_info::text AS information
FROM students_json;
This displays the JSONB value as text.
Sometimes JSON information is stored in a text value. We can convert it into JSONB using a type cast.
Example:
SELECT
'{"name": "Ali", "age": 22}'::jsonb AS student_data;
The ::jsonb converts the text representation into a JSONB value.
Another example:
SELECT
'{"marks": 85}'::jsonb ->> 'marks' AS marks;
Output:
85
We can use JSONB values in filtering conditions.
Find students whose age is 22:
SELECT *
FROM students_json
WHERE additional_info ->> 'age' = '22';
Find students whose department is Computer Science:
SELECT *
FROM students_json
WHERE additional_info ->> 'department' = 'Computer Science';
For nested department information:
SELECT *
FROM students_json
WHERE additional_info -> 'academic' ->> 'department'
= 'Computer Science';
When a table contains many records, searching JSONB data may become slower.
PostgreSQL supports GIN indexes for JSONB columns.
Example:
CREATE INDEX idx_student_additional_info
ON students_json
USING GIN (additional_info);
This creates a GIN index on the additional_info column.
It can help queries using JSONB operators such as @> and ?.
Note: Index usefulness depends on the query and index type. An index is not guaranteed to improve every query.
PostgreSQL provides several functions for working with JSON and JSONB.
| Function | Purpose |
|---|---|
json_typeof() |
Returns the JSON value type |
jsonb_typeof() |
Returns the JSONB value type |
json_build_object() |
Creates a JSON object |
jsonb_build_object() |
Creates a JSONB object |
json_agg() |
Aggregates values into a JSON array |
jsonb_agg() |
Aggregates values into a JSONB array |
json_array_length() |
Returns the number of array elements |
jsonb_array_length() |
Returns the number of JSONB array elements |
SELECT jsonb_typeof('{"name": "Ali", "age": 22}'::jsonb);
Output:
object
SELECT jsonb_build_object(
'name', 'Ali',
'age', 22,
'department', 'Computer Science'
);
SELECT jsonb_agg(student_name)
FROM students_json;
This creates a JSONB array containing student names.
SELECT jsonb_array_length(
'["C++", "Python", "SQL"]'::jsonb
);
Output:
3
JSONB is useful when:
University example:
A university may store additional student information such as:
{
"student_id": 101,
"skills": ["C++", "Python", "SQL"],
"certifications": [
"Google Data Analytics",
"Python Programming"
],
"address": {
"city": "Dera Ghazi Khan",
"country": "Pakistan"
}
}
This type of information can be stored in a JSONB column.
However, important information such as student ID, department ID, enrollment status, and fee balance should generally use properly designed relational columns and tables.
Exercise 1: Create a table named employee_json with the following columns:
employee_idemployee_nameemployee_info (JSONB)Exercise 2: Insert three employees with JSONB information containing their age and department.
Exercise 3: Display all employee records.
Exercise 4: Retrieve each employee’s department using the ->> operator.
Exercise 5: Retrieve each employee’s age using the ->> operator.
Exercise 6: Insert a JSONB object containing an employee’s skills as an array.
Exercise 7: Retrieve the first skill from the skills array.
Exercise 8: Find employees whose department is IT.
Exercise 9: Check whether an employee’s JSONB data contains the age key.
Exercise 10: Update an employee’s age using jsonb_set().
Exercise 11: Add a new key named joining_year to an employee’s JSONB data.
Exercise 12: Remove the age key from an employee’s JSONB data.
Exercise 13: Create a GIN index on the employee_info column.
Exercise 14: Use jsonb_build_object() to create an employee JSONB object.
Exercise 15: Use jsonb_agg() to return all employee names as a JSONB array.
-> operator?->> operator?? operator?@> operator do?| | Concept | PostgreSQL syntax | \ |
| |— | — | \ |
| | JSON data type | JSON |
\ |
| | JSONB data type | JSONB |
\ |
| | Access JSON value | -> |
\ |
| | Access text value | ->> |
\ |
| | Check key existence | ? |
\ |
| | Check containment | @> |
\ |
| | Update JSONB value | jsonb_set() |
\ |
| | Add or merge keys | || |
\ |
| | Remove a key | - |
\ |
| | Remove nested key | #- |
\ |
| | Create JSONB object | jsonb_build_object() |
\ |
| | Create JSONB array | jsonb_agg() |
\ |
| | Create JSONB index | USING GIN |
Next Lesson: PostgreSQL System and Information Functions
In the next lesson, students will learn how to retrieve information about the current database, connected user, current schema, server version, and current session.