PostgreSQL: PostgreSQL JSON与JSONB数据处理

SELECT '{"name": "Alice", "age": 30}'::json;
-- {"name": "Alice", "age": 30}

SELECT '{"name": "Alice", "age": 30}'::jsonb;
-- {"age": 30, "name": "Alice"}

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  profile JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO users (username, profile) VALUES
  ('alice', '{"theme": "dark", "language": "en", "notifications": true}'),
  ('bob', '{"timezone": "UTC-5", "currency": "USD", "dashboard_layout": "grid"}');

INSERT 0 1

SELECT profile -> 'theme' AS theme_json,
       profile ->> 'theme' AS theme_text
FROM users
WHERE username = 'alice';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT profile #> '{address,city}' AS city_json,
       profile #>> '{address,city}' AS city_text
FROM users
WHERE profile ? 'address';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT username, profile
FROM users
WHERE profile @> '{"theme": "dark"}';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT username, profile ->> 'timezone' AS tz
FROM users
WHERE profile ? 'timezone';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT username
FROM users
WHERE profile ?| array['timezone', 'currency'];

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  attributes JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO products (name, attributes) VALUES
  ('T-Shirt', '{"colors": ["red", "blue", "green"], "sizes": ["S", "M", "L"]}'),
  ('Laptop', '{"colors": ["silver", "black"], "warranty_years": 2}');

SELECT product_id, name,
       jsonb_array_elements_text(attributes -> 'colors') AS color
FROM products;

INSERT 0 1

SELECT username,
       (jsonb_each(profile)).key AS config_key,
       (jsonb_each(profile)).value AS config_value
FROM users;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Add or update a field
UPDATE users
SET profile = jsonb_set(profile, '{language}', '"zh"')
WHERE username = 'alice';

-- Add a nested field
UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"New York"')
WHERE username = 'alice';

-- Delete a field
UPDATE users
SET profile = profile - 'notifications'
WHERE username = 'alice';

-- SQL 语句执行成功

-- Append to end (path must point to existing array, insert after last element)
UPDATE products
SET attributes = jsonb_set(
  attributes, '{colors}',
  (attributes -> 'colors') || '"yellow"'
)
WHERE name = 'T-Shirt';

INSERT 0 1

SELECT jsonb_pretty(profile) FROM users WHERE username = 'alice';

{
    "theme": "dark",
    "language": "zh",
    "address": {
        "city": "New York"
    }
}

-- Default GIN index (supports @>, ?, ?|, ?&)
CREATE INDEX idx_users_profile ON users USING gin (profile);

-- Path ops GIN index (smaller, faster for @> only)
CREATE INDEX idx_users_profile_path ON users USING gin (profile jsonb_path_ops);

CREATE TABLE

-- Without index: sequential scan
EXPLAIN ANALYZE
SELECT * FROM users WHERE profile @> '{"theme": "dark"}';

-- After creating GIN index: bitmap index scan
CREATE INDEX idx_users_profile ON users USING gin (profile);
EXPLAIN ANALYZE
SELECT * FROM users WHERE profile @> '{"theme": "dark"}';

CREATE TABLE

-- For queries using ->> operator
CREATE INDEX idx_users_theme ON users ((profile ->> 'theme'));
SELECT * FROM users WHERE profile ->> 'theme' = 'dark'; -- Uses index

CREATE TABLE

SELECT jsonb_path_query(profile, '$.theme') AS theme
FROM users
WHERE username = 'alice';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Products with warranty > 1 year
SELECT name,
       jsonb_path_query(attributes, '$.warranty_years') AS warranty
FROM products
WHERE jsonb_path_exists(attributes, '$.warranty_years ? (@ > 1)');

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT name,
       jsonb_path_query(attributes, '$.colors[*]') AS color
FROM products;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE TABLE products_v2 (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,              -- Fixed column, indexed
  price NUMERIC(10,2) NOT NULL,        -- Fixed column, indexed
  stock INT NOT NULL DEFAULT 0,        -- Fixed column, indexed
  attributes JSONB NOT NULL DEFAULT '{}', -- Dynamic attributes
  metadata JSONB DEFAULT '{}'          -- Rarely queried meta info
);

CREATE INDEX idx_products_category ON products_v2 (category);
CREATE INDEX idx_products_attrs ON products_v2 USING gin (attributes jsonb_path_ops);

CREATE TABLE

ALTER TABLE products_v2
ADD CONSTRAINT chk_attributes_schema
CHECK (
  jsonb_typeof(attributes -> 'colors') = 'array'
  AND attributes ? 'colors'
);

-- SQL 语句执行成功

-- Find orders where product has specific attribute
SELECT o.order_id, o.customer_id, p.name
FROM orders o
JOIN products_v2 p ON o.product_id = p.product_id
WHERE p.attributes @> '{"warranty_years": 2}';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Step 1: Create core tables with JSONB
CREATE TABLE saas_users (
  user_id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  plan TEXT NOT NULL DEFAULT 'free',
  config JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE saas_products (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  specs JSONB NOT NULL DEFAULT '{}',
  tags JSONB NOT NULL DEFAULT '[]'
);

-- Step 2: Insert sample data
INSERT INTO saas_users (email, name, plan, config) VALUES
  ('alice@corp.com', 'Alice', 'pro',
   '{"theme":"dark","language":"en","notifications":{"email":true,"sms":false},"sidebar":["dashboard","reports"]}'),
  ('bob@corp.com', 'Bob', 'enterprise',
   '{"theme":"light","language":"zh","notifications":{"email":true,"sms":true},"sidebar":["dashboard","admin","billing"]}');

INSERT INTO saas_products (name, category, price, specs, tags) VALUES
  ('Pro Widget', 'widget', 49.99,
   '{"weight_kg":0.5,"colors":["red","blue"],"warranty_years":3}',
   '["popular","new"]'),
  ('Mega Gadget', 'gadget', 199.99,
   '{"weight_kg":2.0,"colors":["silver","black"],"voltage":"220V"}',
   '["premium","bestseller"]');

-- Step 3: Create indexes
CREATE INDEX idx_saas_users_config ON saas_users USING gin (config);
CREATE INDEX idx_saas_products_specs ON saas_products USING gin (specs jsonb_path_ops);
CREATE INDEX idx_saas_products_tags ON saas_products USING gin (tags);
CREATE INDEX idx_saas_products_category ON saas_products (category);

-- Step 4: Query examples
-- Find users with email notifications enabled
SELECT name, config ->> 'theme' AS theme
FROM saas_users
WHERE config @> '{"notifications":{"email":true}}';

-- Find products available in red
SELECT name, price
FROM saas_products
WHERE specs -> 'colors' @> '["red"]';

-- Find products with specific tags
SELECT name
FROM saas_products
WHERE tags @> '["premium"]';

-- Update user config (add new field)
UPDATE saas_users
SET config = jsonb_set(config, '{timezone}', '"America/New_York"')
WHERE email = 'alice@corp.com';

-- Remove a config field
UPDATE saas_users
SET config = config - 'language'
WHERE email = 'bob@corp.com';

-- Expand product tags for analytics
SELECT name, jsonb_array_elements_text(tags) AS tag
FROM saas_products;

-- Pretty print user config
SELECT name, jsonb_pretty(config) FROM saas_users WHERE plan = 'pro';