เก็บ JSON ด้วย JSONB

บางครั้ง row หนึ่งต้องเก็บข้อมูลที่รูปร่างไม่ได้ถูกกำหนดไว้ล่วงหน้า เช่น สินค้าที่มีคุณสมบัติต่างกันไปตามหมวดหมู่ payload ของ event จากบุคคลที่สาม หรือการตั้งค่าของผู้ใช้ที่งอกเพิ่มขึ้นตามเวลา PostgreSQL ให้ column เดียวเก็บเอกสาร JSON ทั้งก้อนด้วยชนิดข้อมูล jsonb แล้ว query เข้าไปข้างในได้ราวกับว่า key แต่ละตัวคือ column

ในบทนี้เราใช้ table ของ products ที่แต่ละ row พกเอกสาร attributes ที่ยืดหยุ่นไว้

การสร้าง column JSONB

ชนิดข้อมูล jsonb เก็บ JSON ในรูปแบบไบนารีที่ถูกแยกย่อยแล้ว (decomposed binary form) เมื่อเทียบกับชนิด json ธรรมดา jsonb จะตัด whitespace ที่ไม่สำคัญทิ้ง ลบ key ซ้ำ ไม่รักษาลำดับของ key และ — ที่สำคัญที่สุด — สามารถทำ index ได้

CREATE TABLE products (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  attributes jsonb NOT NULL DEFAULT '{}'
);

การเก็บเอกสาร

การ insert row ก็เป็น INSERT ธรรมดา เอกสารเป็นเพียง JSON string ที่ PostgreSQL parse ให้เป็น jsonb จากฝั่ง driver คุณส่ง JSON เป็น parameter แทนการต่อ string ด้วยมือ

psql

INSERT INTO products (name, attributes) VALUES
  ('Trail Runner', '{"color": "teal", "sizes": [8, 9, 10], "waterproof": true}'),
  ('City Loafer',  '{"color": "brown", "sizes": [9, 10, 11], "leather": "suede"}');

Node.js

await pool.query(
  'INSERT INTO products (name, attributes) VALUES ($1, $2)',
  ['Trail Runner', { color: 'teal', sizes: [8, 9, 10], waterproof: true }],
);

Python

import json

cur.execute(
    "INSERT INTO products (name, attributes) VALUES (%s, %s)",
    ("Trail Runner", json.dumps({"color": "teal", "sizes": [8, 9, 10], "waterproof": True})),
)

Go

attrs := map[string]any{"color": "teal", "sizes": []int{8, 9, 10}, "waterproof": true}
_, err := conn.Exec(ctx,
    "INSERT INTO products (name, attributes) VALUES ($1, $2)",
    "Trail Runner", attrs)

Rust

let attrs = serde_json::json!({ "color": "teal", "sizes": [8, 9, 10], "waterproof": true });
sqlx::query("INSERT INTO products (name, attributes) VALUES ($1, $2)")
    .bind("Trail Runner")
    .bind(attrs)
    .execute(&pool)
    .await?;

ใน pgAdmin: เซลล์ attributes จะแสดงเอกสาร JSON ดับเบิลคลิกที่เซลล์เพื่อเปิดมุมมอง editor ที่ใหญ่ขึ้นสำหรับดูค่า

การเข้าถึงข้างในเอกสาร

operator ไม่กี่ตัวช่วยดึงค่าออกมาจากค่า jsonb สองตัวที่คุณจะหยิบใช้บ่อยที่สุดคือ -> และ ->>

Operator	คืนค่า	ตัวอย่าง
->	ค่า jsonb (object หรือ array)	attributes -> 'sizes'
->>	ค่าในรูปแบบ text	attributes ->> 'color'
#>	ค่า jsonb ที่ path หนึ่ง	attributes #> '{sizes,0}'
#>>	text ที่ path หนึ่ง	attributes #>> '{sizes,0}'

ความต่างระหว่าง -> กับ ->> นั้นสำคัญ คือ -> คงผลลัพธ์ไว้เป็น jsonb (ดีสำหรับการต่อลงไปในข้อมูลที่ซ้อนลึกขึ้น) ส่วน ->> ให้ text ธรรมดาที่คุณนำไปเทียบกับ string หรือ cast เป็นตัวเลขได้

psql

SELECT name,
       attributes ->> 'color'        AS color,
       attributes -> 'sizes'         AS sizes,
       attributes #>> '{sizes, 0}'   AS smallest_size
FROM products;

Node.js

const res = await pool.query(
  `SELECT name,
          attributes ->> 'color'      AS color,
          attributes -> 'sizes'       AS sizes,
          attributes #>> '{sizes, 0}' AS smallest_size
   FROM products`,
);
console.log(res.rows);

Python

cur.execute(
    """
    SELECT name,
           attributes ->> 'color'      AS color,
           attributes -> 'sizes'       AS sizes,
           attributes #>> '{sizes, 0}' AS smallest_size
    FROM products
    """
)
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
    SELECT name,
           attributes ->> 'color'      AS color,
           attributes -> 'sizes'       AS sizes,
           attributes #>> '{sizes, 0}' AS smallest_size
    FROM products`)

Rust

let rows = sqlx::query(
    "SELECT name, \
            attributes ->> 'color'      AS color, \
            attributes -> 'sizes'       AS sizes, \
            attributes #>> '{sizes, 0}' AS smallest_size \
     FROM products",
)
.fetch_all(&pool)
.await?;

     name     | color |   sizes    | smallest_size
--------------+-------+------------+---------------
 Trail Runner | teal  | [8, 9, 10] | 8
 City Loafer  | brown | [9, 10, 11]| 9
(2 rows)

การกรองด้วย containment และการทดสอบ key

operator สองตัวถูกสร้างมาเพื่อการค้นหา containment operator @> ถามว่าเอกสารฝั่งซ้ายมีเอกสารฝั่งขวาอยู่หรือไม่ ส่วน existence operator ? ถามว่า key ระดับบนสุดมีอยู่หรือไม่

psql

-- products whose document contains color = teal
SELECT name FROM products
WHERE attributes @> '{"color": "teal"}';

-- products that have a top-level "leather" key
SELECT name FROM products
WHERE attributes ? 'leather';

Node.js

const teal = await pool.query(
  "SELECT name FROM products WHERE attributes @> $1",
  [{ color: 'teal' }],
);
const leather = await pool.query(
  "SELECT name FROM products WHERE attributes ? 'leather'",
);

Python

cur.execute(
    "SELECT name FROM products WHERE attributes @> %s",
    (json.dumps({"color": "teal"}),),
)
teal = cur.fetchall()
cur.execute("SELECT name FROM products WHERE attributes ? 'leather'")
leather = cur.fetchall()

Go

rows, err := conn.Query(ctx,
    "SELECT name FROM products WHERE attributes @> $1",
    map[string]any{"color": "teal"})

Rust

let teal = sqlx::query("SELECT name FROM products WHERE attributes @> $1")
    .bind(serde_json::json!({ "color": "teal" }))
    .fetch_all(&pool)
    .await?;

     name
--------------
 Trail Runner
(1 row)

การแก้ไขบางส่วนของเอกสาร

หากต้องการเปลี่ยนฟิลด์เดียวโดยไม่ต้องเขียนเอกสารทั้งก้อนใหม่ ให้ใช้ jsonb_set ที่รับ column, path ในรูปแบบ text array และค่าใหม่ในรูปแบบ jsonb

UPDATE products
SET attributes = jsonb_set(attributes, '{color}', '"midnight"')
WHERE name = 'Trail Runner'
RETURNING name, attributes ->> 'color' AS color;

     name     |  color
--------------+----------
 Trail Runner | midnight
(1 row)

หากต้องการเพิ่มฟิลด์ใหม่เอี่ยม คุณยังใช้ concatenation operator ได้ด้วย: attributes || '{"on_sale": true}' จะรวมเอกสารฝั่งขวาเข้าไปในฝั่งซ้าย

การทำ index ให้ containment query

หากไม่มี index คำสั่ง @> จะ scan ทุก row และทดสอบแต่ละเอกสาร GIN index เปลี่ยนเรื่องนั้น เพราะทำ index ให้ทั้ง key และ value ภายใน jsonb ดังนั้นการค้นหาแบบ containment และ key-existence จึงเร็วขึ้น

CREATE INDEX idx_products_attributes ON products USING gin (attributes);

CREATE INDEX

หลังจากนี้ query ที่ใช้ @>, ?, ?| และ ?& จะใช้ index ได้ ถ้าคุณ query เฉพาะ path ใดเพียง path เดียวเสมอ expression index บน path นั้นจะเล็กกว่าและเร็วกว่าการทำ index ให้ทั้งเอกสาร

เคล็ดลับและข้อควรระวัง

• เลือก jsonb แทน json สำหรับเกือบทุกกรณี ชนิด json เก็บข้อความเป๊ะ ๆ ตามที่คุณส่งมา (รักษา whitespace และลำดับ) แต่ทำ index ไม่ได้ และ parse ใหม่ทุกครั้งที่เข้าถึง
• ใช้ jsonb กับข้อมูลที่แปรผันจริง ๆ ไม่ใช่เป็นทางเลี่ยงการออกแบบ schema ถ้าฟิลด์หนึ่งมีอยู่เสมอและเป็นชนิดเดิมเสมอ column จริงจะให้คุณได้ทั้งการตรวจสอบชนิด ค่า default และ constraint
• เพิ่ม GIN index เฉพาะเมื่อคุณ query เข้าไปข้างในเอกสารจริง ๆ การทำ index ให้ข้อมูลที่คุณอ่านกลับมาทั้งก้อนเท่านั้นเป็นการเปลืองพื้นที่และเพิ่มภาระตอนเขียน
• จำไว้ว่า -> คืน jsonb และ ->> คืน text การเทียบ attributes -> 'color' = 'teal' จะล้มเหลวเพราะฝั่งซ้ายเป็น jsonb จงใช้ ->> สำหรับการเทียบ text

🐘 Document store ภายในฐานข้อมูลเชิงสัมพันธ์ของคุณ

column jsonb ทำให้ table เดียวผสม column ที่เคร่งครัดและมีชนิดข้อมูลชัดเจน เข้ากับฟิลด์เอกสารที่ยืดหยุ่นได้ ความเป็นลูกผสมนั้นมักเป็นความยืดหยุ่นของ schema ทั้งหมดที่แอปพลิเคชันต้องการ — ไม่ต้องมี document database แยกต่างหาก เพียงแค่เก็บส่วนที่คงที่จริง ๆ ไว้ใน column จริงที่ Postgres บังคับใช้ได้

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
jsonb column	schema ยืดหยุ่น เพิ่มหรือลบฟิลด์ได้โดยไม่ต้อง migration	Postgres บังคับ type หรือ foreign key ข้างในเอกสารให้ไม่ได้ ความถูกต้องต้องมาจากฝั่งแอปพลิเคชันเอง
column เชิงสัมพันธ์ปกติ	มี type checking, constraint และ foreign key ที่ Postgres บังคับใช้ให้จริง	ทุกฟิลด์ต้องรู้ล่วงหน้าและทุกการเปลี่ยน schema ต้องผ่าน migration

ข้อผิดพลาดที่พบบ่อย

• ยัดทุกอย่างลงใน JSONB blob ก้อนเดียว — ถ้าข้อมูลมีโครงสร้างจริง ๆ และต้องอ้างอิงไปยัง table อื่น ให้ใช้ column เชิงสัมพันธ์กับ foreign key แทน เพราะถ้าความสัมพันธ์ซ่อนอยู่ใน JSONB แล้ว Postgres จะ validate ให้ไม่ได้
• ลืมทำ GIN index ให้ column ที่ query บ่อย — ไม่มี index แปลว่าทุก containment query ต้อง scan ทั้ง table เพิ่ม USING gin (column) ทันทีที่คุณเริ่ม query เข้าไปข้างในเอกสารเป็นประจำ
• ใช้ JSONB แค่เพื่อเลี่ยงการเขียน migration — ถ้าฟิลด์นั้นมีอยู่เสมอและเป็น type เดิมเสมอ column จริงนั้นปลอดภัยกว่าและอ่านง่ายกว่า JSONB ควรสงวนไว้สำหรับข้อมูลที่แปรผันจริง ๆ

💡 ตัวอย่างจากของจริง

Notion — เก็บเนื้อหา block และ page ที่มีโครงสร้างยืดหยุ่นสูงไว้ใน jsonb เพราะรูปแบบ block เปลี่ยนบ่อยและหลากหลายเกินกว่าจะออกแบบเป็น column ตายตัว ส่วนความเป็นเจ้าของ สิทธิ์การเข้าถึง และโครงสร้าง workspace ยังคงอยู่ใน table เชิงสัมพันธ์ที่เคร่งครัด

Shopify — เก็บ metafield ของสินค้าและ order ที่ผู้ขายแต่ละรายปรับแต่งได้เองไว้ใน jsonb เพื่อรองรับความหลากหลายของร้านค้าโดยไม่ต้องแก้ schema หลักของ platform

ข้อได้เปรียบเชิงปฏิบัติที่สำคัญของ jsonb เหนือชนิด json ธรรมดาคืออะไร?

รักษา whitespace และลำดับของ key ไว้ ทำ index ได้ และเก็บในรูปแบบไบนารีที่แยกย่อยแล้ว ยอมให้มี key ซ้ำได้ เป็นชนิดเดียวที่รับ array ได้

operator ใดทดสอบว่าเอกสาร jsonb มีคู่ key/value ที่กำหนดอยู่หรือไม่?

->> #> @> ||

index ชนิดใดทำให้ containment query ของ jsonb เร็วขึ้น?

B-tree GIN Hash BRIN

Submit

Mark as complete