PostgreSQLda JSONB: JSONni jadvalda qachon va qanday saqlash

Qisqa javob: json yoki jsonb

PostgreSQLda JSON uchun ikki tur bor:

Amaliy qoida: deyarli har doim jsonbni tanlang. json faqat hujjatni bayt-baytigacha saqlash muhim bo‘lsa kerak, masalan kiruvchi so‘rov imzosini tekshirish uchun.

Asosiy operatorlar

Mahsulotlar jadvali bor deylik:

CREATE TABLE products (
  id     bigserial PRIMARY KEY,
  name   text NOT NULL,
  price  numeric(14, 2) NOT NULL,
  attrs  jsonb NOT NULL DEFAULT '{}'
);

INSERT INTO products (name, price, attrs) VALUES
  ('Noutbuk', 1000.00,
   '{"brand": "Lenovo", "ram_gb": 16, "colors": ["black", "grey"], "dims": {"weight_kg": 1.4}}');

• -> qiymatni jsonb sifatida qaytaradi: attrs -> 'dims'.
• ->> qiymatni text sifatida qaytaradi: attrs ->> 'brand'.
• #> va #>> — xuddi shunday, lekin yo‘l bo‘yicha: attrs #>> '{dims,weight_kg}'.
• @> — «o‘z ichiga oladi»: attrs @> '{"brand": "Lenovo"}'.
• ? — yuqori darajadagi kalit bormi: attrs ? 'ram_gb'.

SELECT name, attrs ->> 'brand' AS brand
FROM products
WHERE attrs @> '{"colors": ["black"]}'
  AND (attrs ->> 'ram_gb')::int >= 16;

E’tibor bering: ->> har doim matn qaytaradi, shuning uchun sonlarni solishtirishda turni aniq keltirish kerak.

Indekslar

Butun ustunga GIN indeks @>, ?, ?| va ?& operatorlarini tezlashtiradi:

CREATE INDEX products_attrs_gin ON products USING GIN (attrs);

Agar faqat @> orqali qidirsangiz, jsonb_path_ops operator sinfidan foydalaning — indeks ixchamroq bo‘ladi, lekin ?ni qo‘llab-quvvatlamaydi:

CREATE INDEX products_attrs_path ON products USING GIN (attrs jsonb_path_ops);

Ifoda bo‘yicha B-tree — tez-tez filtrlanadigan yoki saralanadigan bitta kalit uchun:

CREATE INDEX products_ram ON products (((attrs ->> 'ram_gb')::int));

So‘rov indeksdagi ifodaning aynan o‘zini ishlatishi kerak, aks holda rejalashtiruvchi indeksni olmaydi.

Ichki maydonlarni yangilash

-- yo‘l bo‘yicha qiymatni almashtirish
UPDATE products SET attrs = jsonb_set(attrs, '{dims,weight_kg}', '1.3') WHERE id = 1;

-- yuqori darajadagi kalitlarni qo‘shish yoki qayta yozish
UPDATE products SET attrs = attrs || '{"warranty_months": 12}' WHERE id = 1;

-- kalitni va ichki yo‘lni o‘chirish
UPDATE products SET attrs = attrs - 'colors' WHERE id = 1;
UPDATE products SET attrs = attrs #- '{dims,weight_kg}' WHERE id = 1;

Muhim tafsilotlar:

• jsonb_set yo‘lning faqat oxirgi kalitini yaratadi. Oraliq obyekt (dims) bo‘lmasa, hujjat o‘zgarmaydi.
• || operatori faqat yuqori darajani birlashtiradi: ichki obyekt birlashtirilmaydi, butunlay almashtiriladi.
• PostgreSQLning yangi versiyalarida indekslash orqali yangilash mumkin: SET attrs['brand'] = '"HP"'.
• Har qanday o‘zgarish butun hujjatni qayta yozadi. Bitta maydoni tez-tez o‘zgaradigan katta JSONlar ortiqcha yuklama beradi.

JSON ustun qachon o‘rinli

• Mahsulot atributlari: telefonda xotira va diagonal, poyabzalda o‘lcham va material. Umumiy maydonlar (nom, narx, qoldiq, kategoriya) — ustunlar, o‘ziga xoslari — jsonb.
• Payme, Click va boshqa provayderlardan keladigan xom to‘lov webhooklari. Bahsli holatlarni tahlil qilish uchun to‘liq payloadni saqlang, tranzaksiya identifikatori, summa va statusni esa ustunlarga chiqaring:

CREATE TABLE payment_events (
  id           bigserial PRIMARY KEY,
  provider     text NOT NULL,
  external_id  text NOT NULL,
  status       text NOT NULL,
  amount       numeric(14, 2) NOT NULL,
  payload      jsonb NOT NULL,
  received_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (provider, external_id, status)
);

Unikal kalit provayder bitta bildirishnomani ikki marta yuborsa, uni qayta ishlashdan himoya qiladi.

• Sozlamalar va metama’lumotlar — butunligicha o‘qiladigan va kam filtrlanadigan.
• Tashqi API javoblari — formatini siz nazorat qilmaydigan.

Qachon bu antipattern bo‘ladi

Xavotirli belgilar:

• maydon barcha qatorlarda bor va deyarli har bir WHEREda qatnashadi;
• maydon bo‘yicha JOIN qilinadi yoki unda boshqa yozuvlarning id lari saqlanadi — tashqi kalitlar yo‘qoladi;
• cheklovlar kerak: NOT NULL, unikallik, tur, CHECK;
• maydon bo‘yicha pul va hisobotlar hisoblanadi: summalar, guruhlashlar;
• kodning turli qismlari bir xil narsani turli kalitlar ostida yozadi.

Yana bir sabab: rejalashtiruvchi jsonb ichidagi kalitlar bo‘yicha batafsil statistika yig‘maydi, shuning uchun murakkab so‘rovlar uchun baholar noaniq bo‘lishi mumkin. Maydon muhim bo‘lib qolsa, uni migratsiya orqali ustunga chiqaring.