PostgreSQL JSON関数の使用例

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    info JSONB
);

-- Insert a customer with basic information
INSERT INTO customers (info) VALUES (
    '{"name": "Alice Smith", "email": "alice@example.com", "age": 32, "addresses": [
        {"type": "home", "city": "San Francisco", "state": "CA"}, 
        {"type": "work", "city": "Oakland", "state": "CA"}
     ]}'
);

-- Insert another customer
INSERT INTO customers (info) VALUES (
    '{"name": "Bob Johnson", "email": "bob@example.com", "age": 45, "addresses": [
        {"type": "home", "city": "Seattle", "state": "WA"}
     ], "orders": [101, 102, 103]}'
);

SELECT info FROM customers WHERE id = 1;

-- Get the name field as JSON
SELECT info -> 'name' FROM customers;

-- Get the name field as text
SELECT info ->> 'name' AS customer_name FROM customers;

-- Getting the first address (array element 0) city
SELECT info -> 'addresses' -> 0 ->> 'city' AS home_city FROM customers;

-- Using the path syntax to get the same value
SELECT info #> '{addresses,0,city}' FROM customers;

-- Using the path syntax to get the same value as text
SELECT info #>> '{addresses,0,city}' AS home_city FROM customers;

-- Add a new field 'premium_customer' to a customer
UPDATE customers
SET info = jsonb_set(info, '{premium_customer}', 'true')
WHERE id = 1;

-- Remove the premium_customer field
UPDATE customers
SET info = info - 'premium_customer'
WHERE id = 1;

-- Add multiple fields by merging objects
UPDATE customers
SET info = info || '{"status": "active", "last_login": "2025-03-23"}'::jsonb
WHERE id = 1;

-- Update a nested field (home address state)
UPDATE customers
SET info = jsonb_set(
    info, 
    '{addresses,0,state}', 
    '"NY"'::jsonb
)
WHERE id = 1;

-- Get the number of addresses for each customer
SELECT 
    info ->> 'name' AS customer_name,
    jsonb_array_length(info -> 'addresses') AS address_count
FROM customers;

-- Get the second address (index 1) for Alice
SELECT info -> 'addresses' -> 1 FROM customers WHERE id = 1;

-- Unnest the addresses array to query individual addresses
SELECT 
    c.id,
    c.info ->> 'name' AS customer_name,
    jsonb_array_elements(c.info -> 'addresses') AS address
FROM customers c;

-- Find customers with addresses in CA
SELECT DISTINCT
    c.id,
    c.info ->> 'name' AS customer_name
FROM customers c,
     jsonb_array_elements(c.info -> 'addresses') AS addr
WHERE addr ->> 'state' = 'CA';

-- Find customers who have the 'orders' key
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info ? 'orders';

-- Find customers with a specific order number
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info -> 'orders' ? '101';

-- Find customers who have an address in Oakland (using path operators)
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info @> '{"addresses": [{"city": "Oakland"}]}';

-- Create a GIN index for faster containment operations
CREATE INDEX idx_customers_info ON customers USING GIN (info);

-- Create an index on the 'email' field for fast lookups
CREATE INDEX idx_customers_email ON customers ((info ->> 'email'));

-- This query can use the index we created above
SELECT id, info ->> 'name' AS customer_name
FROM customers
WHERE info ->> 'email' = 'alice@example.com';

-- Extract JSON fields into a tabular structure
SELECT 
    id,
    rec.name,
    rec.email,
    rec.age
FROM 
    customers,
    jsonb_to_record(info) AS rec(
        name text,
        email text,
        age int
    );

-- Extract addresses from all customers into a table
SELECT 
    c.id,
    c.info ->> 'name' AS customer_name,
    addr.type AS address_type,
    addr.city,
    addr.state
FROM 
    customers c,
    jsonb_to_recordset(c.info -> 'addresses') AS addr(
        type text,
        city text,
        state text
    );

-- Aggregate customer orders into a JSON array
SELECT
    c.id,
    c.info ->> 'name' AS customer_name,
    json_agg(o.order_id) AS orders
FROM 
    customers c
    CROSS JOIN LATERAL (
        SELECT jsonb_array_elements_text(c.info -> 'orders') AS order_id
    ) o
GROUP BY c.id, c.info ->> 'name';