7-JSON.sql

/*
This data comes from a LinkedIn Learning training called Intermediate SQL for Data Scientists
This script refers to the 'JSON' module
*/

/******************************************************************
-- Summary of JSON in Data Science and SQL:
-- • JSON is widely used in data science because it is flexible and works well with databases,
--   applications, APIs, and third-party services.
-- • Modern relational databases such as PostgreSQL, MySQL, and Oracle support JSON data types.
-- • JSON allows storing semi-structured data alongside traditional structured relational data.
-- • Relational databases still provide benefits like transactions, consistency, and data integrity.
-- • JSON schemas are adaptable, allowing new fields to be added without redesigning tables.
-- • JSON supports variable attributes, making it useful for product catalogs and mixed data models.
-- • Common use cases include API responses, configuration settings, and user preferences.
-- • JSON naturally handles hierarchical and nested data structures better than traditional tables.
-- • Best practice: use JSON for changing, flexible, or nested data structures.
-- • Use standard relational columns for fixed schemas, keys, relationships, and constraints.
-- • JSON extends relational databases by adding flexibility while preserving core SQL strengths.

-- Summary of JSON vs JSONB in PostgreSQL:
-- • PostgreSQL provides two JSON data types: JSON and JSONB.
-- • JSON is the original text-based format that stores the exact input string.
-- • JSON preserves formatting, whitespace, and character order exactly as received.
-- • JSON is useful when an exact copy of the source data must be retained.
-- • Common JSON use cases include logs, auditing records, and raw API responses.
-- • JSON requires re-parsing the text each time values or paths are queried.
-- • Re-parsing makes JSON slower for searches, filtering, and frequent reads.
-- • JSONB stores JSON in a binary processed format for better performance.
-- • JSONB allows faster querying, searching, and read operations.
-- • JSONB write/insert operations may be slightly slower due to parsing on storage.
-- • JSONB supports indexes such as GIN indexes for efficient lookups.
-- • Use JSONB when filtering or querying fields inside the JSON structure.
-- • Use JSON when preserving the exact original text is more important than speed.
*******************************************************************/

-- Create a table named api_responses in the data_sci schema
-- The table stores:
--   id        = unique identifier for each response
--   response  = JSON object containing API response data
CREATE TABLE data_sci.api_responses (
    id INTEGER PRIMARY KEY,
    response JSON
);

-- Insert a sample API response into the table
-- The JSON contains:
--   status   = request result
--   code     = HTTP response code
--   data     = nested object with user details and metadata
INSERT INTO data_sci.api_responses (id, response)
VALUES (
    1,
    '{
        "status": "success",
        "code": 200,
        "data": {
            "user": {
                "id": 123,
                "name": "Jane Doe",
                "email": "jd@example.com"
            },
            "metadata": {
                "timestamp": "2025-01-30T10:30:00Z",
                "source": "user_api"
            }
        }
    }'
);

-- Retrieve the record with id = 1
-- Returns the stored JSON response
SELECT id, response
FROM data_sci.api_responses
WHERE id = 1;

-- Select the record ID
-- Extract the user's name from the nested JSON response column
-- ->  returns a JSON object
-- ->> returns the text value
SELECT 
    id,
    response->'data'->'user'->>'name' AS user_name
FROM data_sci.api_responses
WHERE id = 1;

-- Remove the existing api_responses table if it exists
DROP TABLE data_sci.api_responses;

-- Recreate the table using JSONB instead of JSON
-- JSONB stores data in binary format, allowing faster querying,
-- indexing, and better performance for JSON operations
CREATE TABLE data_sci.api_responses (
    id INTEGER PRIMARY KEY,
    response JSONB
);

-- Insert a sample API response record
-- The JSONB document contains status, code,
-- nested user information, and metadata
INSERT INTO data_sci.api_responses (id, response)
VALUES (
    1,
    '{
        "status": "success",
        "code": 200,
        "data": {
            "user": {
                "id": 123,
                "name": "Jane Doe",
                "email": "jd@example.com"
            },
            "metadata": {
                "timestamp": "2025-01-30T10:30:00Z",
                "source": "user_api"
            }
        }
    }'
);

-- Create a functional index on the nested user name field
-- This improves performance when filtering or searching by name
CREATE INDEX idx_username 
ON data_sci.api_responses 
((response->'data'->'user'->>'name'));

-- Select the record ID
-- Extract the source value from the nested metadata object inside the JSONB column
-- ->  returns a JSON object
-- ->> returns the text value
SELECT
    id,
    response->'data'->'metadata'->>'source' AS source
FROM data_sci.api_responses;