PostgreSQL: التعامل مع بيانات JSON و JSONB في PostgreSQL

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);

-- فهرس 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)

-- منتجات بضمان > سنة واحدة
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';