JSONB و Full-Text Search فارسی

JSONB و Full-Text Search دو قابلیت قدرتمند PostgreSQL هستند که آن را به یک دیتابیس همه‌فن‌حریف تبدیل می‌کنند. در این فصل عمیق وارد JSONB، عملگرها، JSONPath، GIN indexing و سپس FTS با چالش‌های زبان فارسی می‌شویم.

یادآوری JSONB

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    data JSONB
);

INSERT INTO products(name, data) VALUES
('فرش کاشان', '{
    "color": "قرمز",
    "size": "3x4",
    "materials": ["پشم", "ابریشم"],
    "specs": {
        "knot_count": 250000,
        "weight_kg": 25,
        "dimensions": {"length": 4, "width": 3}
    },
    "tags": ["دستباف", "اصیل", "کاشان"]
}');

عملگرهای JSONB

توابع جستجو

-- jsonb_set - تنظیم مقدار
UPDATE products 
SET data = jsonb_set(data, '{color}', '"آبی"')
WHERE id = 1;

-- nested
UPDATE products 
SET data = jsonb_set(data, '{specs,weight_kg}', '30')
WHERE id = 1;

-- ایجاد path اگر وجود ندارد (پارامتر سوم: create_missing)
UPDATE products 
SET data = jsonb_set(data, '{warranty,years}', '10', true)
WHERE id = 1;

-- jsonb_insert - فقط اگر key وجود ندارد
UPDATE products 
SET data = jsonb_insert(data, '{discount}', '0.15');

-- اطلاعات JSONB
SELECT 
    jsonb_typeof(data),                          -- object/array/string/number
    jsonb_array_length(data->'tags'),
    jsonb_object_keys(data),
    jsonb_pretty(data)
FROM products;

JSONPath – PG 12+

JSONPath زبان قدرتمند برای query پیچیده JSONB:

-- jsonb_path_query - مثل XPath برای JSON
SELECT jsonb_path_query(data, '$.materials[*]') FROM products WHERE id = 1;
-- "پشم"
-- "ابریشم"

-- با فیلتر
SELECT jsonb_path_query(data, '$.specs.knot_count ? (@ > 100000)') 
FROM products;

-- jsonb_path_exists - boolean
SELECT id, name FROM products
WHERE jsonb_path_exists(data, '$.specs.knot_count ? (@ > 200000)');

-- jsonb_path_match - برای @@ operator
SELECT * FROM products
WHERE data @@ '$.color == "قرمز" && $.specs.weight_kg > 20';

-- متغیرهای bind
SELECT jsonb_path_query(data, '$.specs.knot_count ? (@ > $min)', '{"min": 200000}')
FROM products;

ایندکس‌گذاری JSONB

GIN با jsonb_ops (پیش‌فرض)

CREATE INDEX idx_products_data ON products USING GIN (data);

-- پشتیبانی: @>, ?, ?|, ?&
SELECT * FROM products WHERE data @> '{"color":"قرمز"}';
SELECT * FROM products WHERE data ? 'discount';

GIN با jsonb_path_ops (سریع‌تر برای @>)

CREATE INDEX idx_products_data_path ON products USING GIN (data jsonb_path_ops);
-- ~3 برابر کوچک‌تر و سریع‌تر برای @>
-- اما ? و ?| و ?& را پشتیبانی نمی‌کند

B-Tree روی فیلد خاص

-- اگر همیشه روی یک فیلد جستجو می‌کنید
CREATE INDEX idx_products_color ON products ((data->>'color'));

-- query سریع
SELECT * FROM products WHERE data->>'color' = 'قرمز';

-- expression index برای cast
CREATE INDEX idx_products_knot_count 
ON products (((data->'specs'->>'knot_count')::int));

SELECT * FROM products 
WHERE (data->'specs'->>'knot_count')::int > 200000;

aggregation با JSONB

-- jsonb_agg - آرایه از ردیف‌ها
SELECT 
    category,
    jsonb_agg(jsonb_build_object('id', id, 'name', name, 'price', price))
FROM products
GROUP BY category;

-- jsonb_object_agg - تبدیل به object
SELECT jsonb_object_agg(name, price) FROM products;
-- {"فرش کاشان": 5000000, "گلیم": 1500000, ...}

-- jsonb_build_object
SELECT jsonb_build_object(
    'id', id,
    'name', name,
    'tags', data->'tags',
    'discount_price', price * 0.9
) FROM products;

-- to_jsonb - ساده‌ترین راه تبدیل
SELECT to_jsonb(p) FROM products p;

-- row_to_json (نسخه قدیمی)
SELECT row_to_json(p) FROM products p;

unnest کردن JSONB

-- jsonb_array_elements - عناصر آرایه به ردیف
SELECT id, name, jsonb_array_elements(data->'tags') AS tag
FROM products;

-- پیدا کردن همه tag‌های یکتا
SELECT DISTINCT jsonb_array_elements_text(data->'tags') AS tag
FROM products;

-- jsonb_each - هر key-value یک ردیف
SELECT key, value 
FROM products, jsonb_each(data)
WHERE id = 1;

Full-Text Search – معرفی

FTS یعنی جستجو در متن با درک stemming، stop words و relevance ranking – نه فقط LIKE.

tsvector و tsquery

-- to_tsvector: متن را به tokens تبدیل می‌کند
SELECT to_tsvector('english', 'The quick brown foxes jump over the lazy dogs');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

-- stop words حذف، stemming اعمال می‌شود (foxes → fox)

-- to_tsquery: query
SELECT to_tsquery('english', 'fox & dog');     -- 'fox' & 'dog'
SELECT to_tsquery('english', 'fox | cat');     -- 'fox' | 'cat'
SELECT to_tsquery('english', '!cat');          -- !'cat'
SELECT to_tsquery('english', 'fox <-> jump');  -- adjacent

-- match با @@
SELECT to_tsvector('english', 'foxes are smart') 
       @@ to_tsquery('english', 'fox');     -- true

FTS برای فارسی

متاسفانه PostgreSQL config رسمی برای فارسی ندارد. سه راه‌حل:

راه ۱: simple config (بدون stemming)

-- 'simple' فقط lowercase می‌کند، stop words و stemming ندارد
SELECT to_tsvector('simple', 'فرش‌های دستباف کاشان');
-- 'دستباف':2 'فرش‌های':1 'کاشان':3

-- چالش: 'فرش' و 'فرش‌ها' متفاوت هستند

راه ۲: pg_trgm (پیشنهاد – بهترین برای فارسی)

CREATE EXTENSION pg_trgm;

-- ایندکس برای جستجوی fuzzy
CREATE INDEX idx_products_name_trgm 
ON products USING GIN (name gin_trgm_ops);

-- جستجوی شباهت
SELECT *, similarity(name, 'فرش کشان') AS sim 
FROM products
WHERE name % 'فرش کشان'             -- % = شبیه است
ORDER BY sim DESC;
-- حتی غلط املایی را پیدا می‌کند

-- LIKE با wildcard اول حالا سریع است
SELECT * FROM products WHERE name ILIKE '%کاشان%';

-- ترکیب با threshold
SET pg_trgm.similarity_threshold = 0.3;
SELECT * FROM products WHERE name % 'فرش';

راه ۳: ساخت dictionary سفارشی فارسی

کاری پیشرفته – نیاز به snowball یا lemmatizer فارسی. در عمل، pg_trgm کافی است.

FTS کامل روی جدول

-- روش ۱: ستون tsvector جدا
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    search_vector TSVECTOR
);

-- پر کردن دستی
UPDATE articles 
SET search_vector = 
    setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(body, '')), 'B');

-- ایندکس
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- trigger برای auto-update
CREATE OR REPLACE FUNCTION articles_search_trigger() 
RETURNS trigger AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('simple', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(NEW.body, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

روش ۲: Generated Column (PG 12+)

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    search_vector TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(body, '')), 'B')
    ) STORED
);

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
-- خودکار به‌روز می‌شود

Ranking با ts_rank

-- مرتب‌سازی نتایج بر اساس relevance
SELECT id, title, 
       ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('simple', 'فرش & کاشان') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

-- ts_rank_cd - cover density ranking (دقیق‌تر)
SELECT id, title, ts_rank_cd(search_vector, query) AS rank
FROM articles, to_tsquery('simple', 'فرش') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- weight‌ها در ts_rank
-- A=1.0, B=0.4, C=0.2, D=0.1 (پیش‌فرض)
SELECT ts_rank('{0.1, 0.2, 0.4, 1.0}', search_vector, query) AS rank
FROM articles, ...;

websearch_to_tsquery (PG 11+)

این تابع syntax انسانی‌تری دارد – مثل گوگل:

-- syntax مثل کاربر تایپ می‌کند
SELECT websearch_to_tsquery('simple', 'فرش کاشان');
-- 'فرش' & 'کاشان'

SELECT websearch_to_tsquery('simple', '"فرش دستباف" کاشان');
-- 'فرش' <-> 'دستباف' & 'کاشان'        -- phrase

SELECT websearch_to_tsquery('simple', 'فرش -ابریشم');
-- 'فرش' & !'ابریشم'                  -- exclude

SELECT websearch_to_tsquery('simple', 'فرش OR گلیم');
-- 'فرش' | 'گلیم'

-- کاربرد در query
SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('simple', 'فرش "دستباف اصیل"');

Highlight نتایج با ts_headline

SELECT id, title, 
       ts_headline('simple', body, query, 'StartSel=<mark>, StopSel=</mark>, MaxWords=30, MinWords=15')
FROM articles, websearch_to_tsquery('simple', 'فرش کاشان') query
WHERE search_vector @@ query;
-- ...<mark>فرش</mark> دستباف <mark>کاشان</mark> با کیفیت...

ترکیب JSONB و FTS

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    data JSONB,
    search_vector TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', coalesce(name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(description, '')), 'B') ||
        setweight(to_tsvector('simple', coalesce(data->>'tags', '')), 'C')
    ) STORED
);

CREATE INDEX idx_p_search ON products USING GIN (search_vector);
CREATE INDEX idx_p_data ON products USING GIN (data jsonb_path_ops);

-- query پیچیده
SELECT id, name, ts_rank(search_vector, query) AS rank
FROM products, websearch_to_tsquery('simple', 'فرش دستباف') query
WHERE search_vector @@ query
  AND data @> '{"available": true}'
  AND (data->>'price')::numeric < 10000000
ORDER BY rank DESC
LIMIT 20;

نکات Performance

• برای @> از jsonb_path_ops (سریع‌تر، کوچک‌تر)
• برای ?, ?|, ?& از jsonb_ops
• FTS با Generated Column ساده‌تر و خودکار
• برای فارسی، pg_trgm + simple بهترین ترکیب
• partial index برای داده فعال

بهترین شیوه‌ها

• JSONB به‌جای JSON همیشه
• اگر schema می‌دانید، در ستون‌های جدا نگه دارید (سریع‌تر)
• JSONB برای داده flexible یا nested
• Generated Column برای search_vector
• websearch_to_tsquery از to_tsquery user-friendly‌تر
• برای فارسی pg_trgm با gin_trgm_ops
• ts_rank برای ordering، ts_headline برای نمایش

جمع‌بندی

• JSONB با عملگرهای @>, ?, ->, #>, jsonb_set
• JSONPath برای query پیچیده
• GIN با jsonb_path_ops برای @>، با jsonb_ops برای ?
• FTS با to_tsvector و to_tsquery
• برای فارسی: simple config + pg_trgm
• websearch_to_tsquery برای syntax انسانی
• ts_rank، ts_headline برای ranking و highlighting