Learn Introduction to JSON Functions

JSON, or JavaScript Object Notation, is a lightweight data-interchange format that is easy for both humans and machines to read and write. In modern databases, the ability to store and query semi-structured data has become increasingly important. Many applications today capture complex user profiles, preferences, and settings in flexible formats, and the JSON data type allows you to store such information directly within a single column. This approach makes it easier to evolve your database schema over time and to handle data that does not fit neatly into traditional tables.


              12345678910111213141516
            
-- Insert a new user with JSON profile data
INSERT INTO user_profiles (user_id, profile_data)
VALUES (6, '{
    "age": 30,
    "interests": ["photography", "travel"],
    "address": {
        "street": "987 Elm St",
        "city": "Riverdale",
        "state": "OH",
        "zip": "43055"
    }
}');

-- Select all user IDs and their profile data
SELECT user_id, profile_data
FROM user_profiles;

To work with JSON data in PostgreSQL, you use specialized functions designed for handling JSON and JSONB columns. Three of the most commonly used PostgreSQL functions are:

jsonb_extract_path_text: extracts a scalar value (such as a string or number) from a JSONB object at a specified path;
jsonb_extract_path: retrieves a JSON object or array from a JSONB column, preserving its structure;
jsonb_build_object: constructs a new JSON object from key-value pairs in your query.

These functions let you access specific values inside JSON documents, return nested objects or arrays, or build new JSON objects directly from relational data.


              1234
            
-- Extract the 'city' from the 'address' object within profile_data for user_id 1 using PostgreSQL JSONB functions
SELECT jsonb_extract_path_text(profile_data::jsonb, 'address', 'city') AS city
FROM user_profiles
WHERE user_id = 1;

1. Which PostgreSQL function extracts a scalar value such as a string or number from a JSONB object at a specified path?

2. What does the PostgreSQL function jsonb_extract_path return when used on a JSONB column?

3. Which PostgreSQL function constructs a new JSON object from key-value pairs in a query?

Everything was clear?

Thanks for your feedback!

Section 6. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

How can I extract other fields from the JSON data, like 'state' or 'zip'?

Can I filter users based on a value inside the JSON, such as all users from a specific city?

What is the difference between JSON and JSONB in PostgreSQL?

Swipe to show menu


              12345678910111213141516
            
-- Insert a new user with JSON profile data
INSERT INTO user_profiles (user_id, profile_data)
VALUES (6, '{
    "age": 30,
    "interests": ["photography", "travel"],
    "address": {
        "street": "987 Elm St",
        "city": "Riverdale",
        "state": "OH",
        "zip": "43055"
    }
}');

-- Select all user IDs and their profile data
SELECT user_id, profile_data
FROM user_profiles;