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)

-- フィールドを追加または更新
UPDATE users
SET profile = jsonb_set(profile, '{language}', '"zh"')
WHERE username = 'alice';

-- ネストされたフィールドを追加
UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"New York"')
WHERE username = 'alice';

-- フィールドを削除
UPDATE users
SET profile = profile - 'notifications'
WHERE username = 'alice';

-- SQL statement executed successfully

-- 末尾に追加（パスは既存の配列を指し、最後の要素の後に挿入）
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"
    }
}

-- デフォルトGINインデックス（@>, ?, ?|, ?&をサポート）
CREATE INDEX idx_users_profile ON users USING gin (profile);

-- Path ops GINインデックス（@>のみ、より小さく高速）
CREATE INDEX idx_users_profile_path ON users USING gin (profile jsonb_path_ops);

CREATE TABLE

-- インデックスなし: 逐次スキャン
EXPLAIN ANALYZE
SELECT * FROM users WHERE profile @> '{"theme": "dark"}';

-- GINインデックス作成後: ビットマップインデックススキャン
CREATE INDEX idx_users_profile ON users USING gin (profile);
EXPLAIN ANALYZE
SELECT * FROM users WHERE profile @> '{"theme": "dark"}';

CREATE TABLE

-- ->> 演算子を使用するクエリ用
CREATE INDEX idx_users_theme ON users ((profile ->> 'theme'));
SELECT * FROM users WHERE profile ->> 'theme' = 'dark'; -- インデックスを使用

CREATE TABLE

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

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

-- 保証期間が1年超の商品
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,              -- 固定カラム、インデックス付き
  price NUMERIC(10,2) NOT NULL,        -- 固定カラム、インデックス付き
  stock INT NOT NULL DEFAULT 0,         -- 固定カラム、インデックス付き
  attributes JSONB NOT NULL DEFAULT '{}', -- 動的属性
  metadata JSONB DEFAULT '{}'          -- ほとんど検索されないメタ情報
);

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 statement executed successfully

-- 特定の属性を持つ商品の注文を検索
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)

-- ステップ1: 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 '[]'
);

-- ステップ2: サンプルデータを挿入
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"]');

-- ステップ3: インデックスを作成
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);

-- ステップ4: クエリ例
-- メール通知が有効なユーザーを検索
SELECT name, config ->> 'theme' AS theme
FROM saas_users
WHERE config @> '{"notifications":{"email":true}}';

-- 赤色が利用可能な商品を検索
SELECT name, price
FROM saas_products
WHERE specs -> 'colors' @> '["red"]';

-- 特定のタグを持つ商品を検索
SELECT name
FROM saas_products
WHERE tags @> '["premium"]';

-- ユーザー設定を更新（新しいフィールドを追加）
UPDATE saas_users
SET config = jsonb_set(config, '{timezone}', '"America/New_York"')
WHERE email = 'alice@corp.com';

-- 設定フィールドを削除
UPDATE saas_users
SET config = config - 'language'
WHERE email = 'bob@corp.com';

-- 分析用に商品タグを展開
SELECT name, jsonb_array_elements_text(tags) AS tag
FROM saas_products;

-- ユーザー設定を整形表示
SELECT name, jsonb_pretty(config) FROM saas_users WHERE plan = 'pro';