Menu

JSONB ב-PostgreSQL: אופרטורים, שאילתות ואינדקסים

שמירה ושאילתות של JSON ב-PostgreSQL עם jsonb: האופרטורים -> ו-->>, הכלה עם @>, עדכונים עם jsonb_set, הפיכת מערכים לשורות, אינדקסי GIN, ו-json מול jsonb.

בדף הזה יש עורכים שאפשר להריץ. לערוך, להריץ ולראות את הפלט מיד.

jsonb הוא טיפוס העמודה של PostgreSQL ל-JSON בפורמט בינארי. קוראים שדות עם -> (מחזיר jsonb) ועם ->> (מחזיר text), מוצאים שורות עם אופרטור ההכלה @>, ומשנים שדות עם jsonb_set או ||. אינדקס GIN הופך את השאילתות האלה למהירות.

הזמנות 1 ו-3 מתאימות. data->'customer' מחזיר את האובייקט המקונן כ-jsonb, ->>'name' שולף את השם כטקסט, ו-->> יחד עם המרה הופכים את הסכום למספר.

שליפת ערכים מתוך JSONB

אופרטורמחזירדוגמה
-> מפתח או אינדקסjsonbdata->'customer', data->'tags'->0
->> מפתח או אינדקסtextdata->>'status'
#> נתיבjsonbdata#>'{customer,city}'
#>> נתיבtextdata#>>'{customer,city}'
[...] subscript (PostgreSQL 14+)jsonbdata['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

jsonjsonb
נשמר כטקסט הקלטצורה בינארית מפוענחת
רווחים, סדר מפתחות, מפתחות כפוליםנשמריםמוסרים; הכפול האחרון גובר
כתיבהמעט מהירה יותר (רק אימות)מפוענח בקלט
קריאה ושאילתותמפוענח מחדש בכל גישהמהירות
@>, ? ואינדקסי 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')), אבל מפתח זר דורש עמודה אמיתית. שדות שיש לכל שורה ושטבלאות אחרות מפנות אליהם שייכים לעמודות רגילות.

איור של שפות התכנות ב-Coddy

ללמוד תכנות עם Coddy

להתחיל