jsonb و آرایه در کوئری: عملگرها، jsonpath و توابع

کار با داده‌ی نیمه‌ساخت‌یافته

عملگرهای jsonb

SELECT title,
       (specs ->> 'reed')::int AS reed,
       specs['yarn'] AS yarn_json          -- subscripting از نسخه‌ی 14
FROM products
WHERE specs @> '{"yarn": "اکریلیک"}'
  AND (specs ->> 'density')::int >= 3000;

UPDATE products
SET specs = jsonb_set(specs, '{finish}', '"براق"', true)
WHERE id = 1;

باز کردن jsonb به ردیف

عیوب کنترل کیفیت را به‌صورت {"رج_کشی": 2, "پرز_دهی": 1} ذخیره کرده‌ایم. مجموع هر نوع عیب در ماه:

SELECT e.key AS defect, sum(e.value::int) AS total
FROM qc_inspections q
CROSS JOIN LATERAL jsonb_each_text(q.defects) AS e(key, value)
WHERE q.inspected_at >= timestamptz '2026-09-23 00:00+03:30'
GROUP BY e.key ORDER BY total DESC;

-- ساختن JSON برای API در خود دیتابیس
SELECT jsonb_build_object('order', o.id,
         'items', jsonb_agg(jsonb_build_object('design', oi.design_code, 'qty', oi.qty) ORDER BY oi.id))
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 501 GROUP BY o.id;

jsonpath

-- بازرسی‌هایی که دست‌کم یک عیب با تعداد بیش از ۲ دارند
SELECT id FROM qc_inspections WHERE defects @? '$.* ? (@ > 2)';
SELECT id, jsonb_path_query(defects, '$.keyvalue() ? (@.value >= 2).key') FROM qc_inspections;

نسخه‌ی 17 توابع استاندارد SQL/JSON را کامل کرده است: JSON_VALUE، JSON_QUERY، JSON_EXISTS و JSON_TABLE که یک سند را مستقیم به جدول ستون‌دار تبدیل می‌کند.

توابع آرایه

SELECT code, t.tag, t.pos
FROM designs, unnest(tags) WITH ORDINALITY AS t(tag, pos);

SELECT array_agg(DISTINCT city ORDER BY city) FROM customers;
SELECT string_to_array('لاکی،سرمه‌ای،کرم', '،');
UPDATE designs SET tags = array_remove(tags, 'قدیمی') WHERE 'قدیمی' = ANY (tags);
SELECT code FROM designs WHERE tags && ARRAY['کلاسیک', 'افشان'];   -- دست‌کم یک برچسب مشترک

نکته‌هایی که کمتر کسی می‌داند

• specs ->> 'reed' = '1200' مقایسه‌ی متنی است؛ برای مقایسه‌ی عددی cast کنید. اما @> '{"reed":1200}' نوع JSON را رعایت می‌کند و با GIN ایندکس می‌شود؛ هرجا ممکن است از @> استفاده کنید.
• jsonb_set اگر مقدار جدید NULL (SQL) باشد کل نتیجه را NULL می‌کند و ستون پاک می‌شود! از jsonb_set_lax یا coalesce(to_jsonb(x), 'null') استفاده کنید.
• subscripting در UPDATE هم کار می‌کند: UPDATE products SET specs['yarn'] = '"پشم"' و مسیرهای میانی ناموجود را خودش می‌سازد.
• jsonb_strip_nulls کلیدهای با مقدار null را حذف می‌کند؛ قبل از ذخیره‌ی پاسخ‌های حجیم درگاه‌ها حجم را محسوس کم می‌کند.
• عملگر @> روی آرایه‌ی jsonb هم کار می‌کند: '["a","b","c"]'::jsonb @> '["b"]'؛ برای برچسب‌های داخل jsonb نیازی به unnest نیست.