jsonb הוא טיפוס העמודה של PostgreSQL ל-JSON בפורמט בינארי. קוראים שדות עם -> (מחזיר jsonb) ועם ->> (מחזיר text), מוצאים שורות עם אופרטור ההכלה @>, ומשנים שדות עם jsonb_set או ||. אינדקס GIN הופך את השאילתות האלה למהירות.
הזמנות 1 ו-3 מתאימות. data->'customer' מחזיר את האובייקט המקונן כ-jsonb, ->>'name' שולף את השם כטקסט, ו-->> יחד עם המרה הופכים את הסכום למספר.
שליפת ערכים מתוך JSONB
| אופרטור | מחזיר | דוגמה |
|---|---|---|
-> מפתח או אינדקס | jsonb | data->'customer', data->'tags'->0 |
->> מפתח או אינדקס | text | data->>'status' |
#> נתיב | jsonb | data#>'{customer,city}' |
#>> נתיב | text | data#>>'{customer,city}' |
[...] subscript (PostgreSQL 14+) | jsonb | data['customer']['city'] |
אינדקסים של מערך מתחילים ב-0, ואינדקס שלילי סופר מהסוף (-> -1 הוא האיבר האחרון). מפתח חסר מחזיר NULL של SQL ולא שגיאה, ולכן להזמנה 3 שלמעלה יש עיר NULL.
השתמשו ב-->> כדי להשוות לטקסט או כדי להמיר: data->>'status' = 'paid'. השתמשו ב--> כדי להמשיך לנווט. טעות נפוצה היא data->'status' = 'paid'. PostgreSQL קורא את 'paid' כ-JSON, מילה בלי מירכאות היא לא JSON תקין, והשאילתה נכשלת עם "invalid input syntax for type json".
חיפוש שורות לפי תוכן ה-JSON
@> בודק אם הערך משמאל מכיל את הערך מימין. הוא עובד בכל עומק ובתוך מערכים, ואינדקס GIN תומך בו:
למוצרים 1 ו-2 יש מידה M ומחיר; רק 2 עולה יותר מ-30. אופרטורי החיפוש:
@>מכיל,<@מוכל בתוך.?המפתח (או מחרוזת במערך) קיים,?|אחד מהמפתחות קיים,?&כולם קיימים.@?ו-@@מריצים שאילתת JSON path (PostgreSQL 12+), להשוואות בתוך המסמך.
עדכון שדות בתוך JSONB
PostgreSQL כותב מחדש את כל הערך בכל עדכון, אבל הפונקציות האלה מאפשרות לתאר רק את השינוי:
jsonb_set(target, path, new_value)מחליף ערך אחד בנתיב.-מסיר מפתח, ו-#-מסיר נתיב מקונן כמו'{notify,sms}'.||מוסיף מפתחות ברמה העליונה או דורס אותם.
מאז PostgreSQL 14 אפשר גם להציב דרך subscript: SET prefs['notify']['email'] = 'false'.
שתי מלכודות:
jsonb_setמחזירNULLכשהערך החדש הואNULLשל SQL, ואז ה-UPDATEמוחק את כל העמודה. העבירוCOALESCE(to_jsonb(x), 'null'), או השתמשו ב-jsonb_set_lax(PostgreSQL 13+), ששומר כברירת מחדלnullשל JSON.jsonb_setמוסיף מפתח אחרון שחסר, אבל לא הורים שחסרים: הצבה של'{a,b}'על{}משאירה אותו בלי שינוי. הצבה דרך subscript כן יוצרת אותם.
הפיכת מערכי JSON לשורות
jsonb_array_elements מחזירה שורה לכל איבר במערך, ו-jsonb_to_recordset הופכת מערך של אובייקטים לעמודות עם טיפוסים. מאז PostgreSQL 17, ה-JSON_TABLE הסטנדרטי עושה את שניהם במקום אחד:
מ-A-1 נמכרו 3 ומ-B-2 נמכר 1. בכיוון ההפוך, jsonb_build_object('id', id, 'name', name) בונה אובייקט מעמודות ו-jsonb_agg אוסף שורות למערך (ראו string_agg ו-array_agg).
אינדקס GIN ל-JSONB
אינדקס GIN על כל העמודה משרת את @>, ?, ?|, ?&, @? ו-@@:
CREATE INDEX orders_data_idx ON orders USING gin (data);
-- smaller and faster, but only for @>, @? and @@
CREATE INDEX orders_data_path_idx ON orders USING gin (data jsonb_path_ops);
-- one field compared with = or a range: a B-tree on the expression
CREATE INDEX orders_status_idx ON orders ((data->>'status'));
כתבו את השאילתה בצורה שהאינדקס תומך בה. WHERE data @> '{"status": "paid"}' יכולה להשתמש באינדקס ה-GIN. WHERE data->>'status' = 'paid' לא יכולה; היא צריכה את האינדקס על הביטוי. בדיקת טווח כמו (data->>'total')::numeric > 50 צריכה אינדקס על הביטוי המדויק הזה.
JSON מול JSONB
json | jsonb | |
|---|---|---|
| נשמר כ | טקסט הקלט | צורה בינארית מפוענחת |
| רווחים, סדר מפתחות, מפתחות כפולים | נשמרים | מוסרים; הכפול האחרון גובר |
| כתיבה | מעט מהירה יותר (רק אימות) | מפוענח בקלט |
| קריאה ושאילתות | מפוענח מחדש בכל גישה | מהירות |
@>, ? ואינדקסי GIN | לא | כן |
json מחזיר את הטקסט בלי שינוי; jsonb משאיר רק את "a": 3 ומסדר מחדש את המפתחות. השתמשו ב-jsonb אלא אם חייבים להחזיר את הטקסט המקורי בית אחר בית, למשל payload שמור של webhook. שדות שיש לכל שורה, או שצריכים מפתח זר, השאירו בעמודות רגילות. המדריך הרשמי לפונקציות JSON מפרט כל אופרטור וכל פונקציה.
שאלות נפוצות
מה ההבדל בין -> ל-->> ב-PostgreSQL?
-> מחזיר את הערך כ-jsonb, כך שאפשר להמשיך לנווט (data->'customer'->'city'). ->> מחזיר אותו כ-text, ובזה משווים למחרוזת או ממירים למספר: (data->>'total')::numeric.
איך בודקים אם מערך JSONB מכיל ערך?
משתמשים בהכלה: data->'tags' @> '["gift"]', או data @> '{"tags": ["gift"]}' כדי לבדוק מראש המסמך. למערך של מחרוזות עובד גם data->'tags' ? 'gift'. אינדקס GIN על data מאיץ את הצורה השנייה.
האם JSONB שומר על סדר המפתחות?
לא. jsonb שומר את המפתחות בסדר משלו, מסיר רווחים ומשאיר רק את האחרון מבין מפתחות כפולים. אם צריך לקבל בחזרה בדיוק את הטקסט המקורי, השתמשו בטיפוס json.
איך בודקים אם מפתח קיים ב-JSONB?
data ? 'key' מחזיר true כשלאובייקט ברמה העליונה יש את המפתח, גם אם הערך שלו הוא null של JSON. ?| מקבל מערך ומחזיר true אם אחד המפתחות קיים, ו-?& אם כולם קיימים.
אפשר להוסיף מפתח זר או אילוץ NOT NULL לשדה בתוך JSONB?
לא ישירות. אילוץ CHECK יכול לבדוק שדה (CHECK (data ? 'email')), אבל מפתח זר דורש עמודה אמיתית. שדות שיש לכל שורה ושטבלאות אחרות מפנות אליהם שייכים לעמודות רגילות.