JSONB

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

ทุกอย่างด้านล่างรันบน PostgreSQL 18.6 (postgres:18.6, aarch64, Docker Desktop บน Mac, กันยายน 2026) fence sql รันจากบนลงล่างในฐานข้อมูลเปล่าหนึ่งตัว ผลลัพธ์คัดลอกจาก psql และเวลาเป็นการรันครั้งเดียวจาก Mac เครื่องนี้

การสร้าง column JSONB

ชนิด jsonb เก็บ JSON ในรูปไบนารีที่แยกส่วนแล้ว เมื่อเทียบกับชนิด json ธรรมดา มันตัดช่องว่างที่ไม่มีความหมาย เก็บเฉพาะ key ซ้ำตัวสุดท้าย ไม่รักษาลำดับ key และที่สำคัญคือ index ได้ บทนี้ใช้ table สินค้าที่แต่ละ row มีเอกสาร attributes ยืดหยุ่น

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

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

insert JSON เป็น string literal PostgreSQL parse และตรวจสอบให้ สังเกตว่า jsonb พิมพ์แต่ละเอกสารอย่างไร: key ออกมาในลำดับของมันเอง (key สั้นก่อน แล้วเรียงตามตัวอักษร) ไม่ใช่ลำดับที่คุณเขียน:

-- sql/insert-documents.sql
INSERT INTO products (name, attributes) VALUES
  ('Trail Jacket',  '{"color": "teal",   "price": 129.5, "tags": ["outdoor", "rain"],    "specs": {"weight_g": 480, "waterproof": true}}'),
  ('City Backpack', '{"color": "black",  "price": 89,    "tags": ["urban"],              "specs": {"weight_g": 900, "waterproof": false}}'),
  ('Camp Stove',    '{"color": "silver", "price": 45,    "tags": ["outdoor", "cooking"], "specs": {"weight_g": 350}}'),
  ('Wool Socks',    '{"color": "teal",   "price": 12.5,  "tags": ["outdoor"]}'),
  ('Rain Poncho',   '{"color": "yellow", "price": 9,     "tags": ["rain", "outdoor"],    "specs": {"waterproof": true}}');
SELECT id, name, attributes FROM products ORDER BY id;

INSERT 0 5
 id |     name      |                                                   attributes
----+---------------+----------------------------------------------------------------------------------------------------------------
  1 | Trail Jacket  | {"tags": ["outdoor", "rain"], "color": "teal", "price": 129.5, "specs": {"weight_g": 480, "waterproof": true}}
  2 | City Backpack | {"tags": ["urban"], "color": "black", "price": 89, "specs": {"weight_g": 900, "waterproof": false}}
  3 | Camp Stove    | {"tags": ["outdoor", "cooking"], "color": "silver", "price": 45, "specs": {"weight_g": 350}}
  4 | Wool Socks    | {"tags": ["outdoor"], "color": "teal", "price": 12.5}
  5 | Rain Poncho   | {"tags": ["rain", "outdoor"], "color": "yellow", "price": 9, "specs": {"waterproof": true}}
(5 rows)

json กับ jsonb ต่างกันตรงประเด็นข้างบนพอดี literal เดียวกัน ที่มีช่องว่างเกินและ key ซ้ำ:

-- sql/json-vs-jsonb.sql
SELECT '{"b": 1,   "a": 2, "a": 3}'::json  AS as_json,
       '{"b": 1,   "a": 2, "a": 3}'::jsonb AS as_jsonb;

          as_json           |     as_jsonb
----------------------------+------------------
 {"b": 1,   "a": 2, "a": 3} | {"a": 3, "b": 1}
(1 row)

json เก็บข้อความตามที่เขียน (ช่องว่าง, key "a" ทั้งสอง, ลำดับ) jsonb ปรับให้เป็นมาตรฐาน: {"a": 3, "b": 1} ตัวซ้ำสุดท้ายชนะ (docs) JSON ที่ไม่ถูกต้องถูกปฏิเสธตอน insert พร้อมตำแหน่งที่แน่นอน:

-- @expect-error 22P02
INSERT INTO products (name, attributes) VALUES ('Broken', '{"color": }');

ERROR:  invalid input syntax for type json
LINE 1: ...NTO products (name, attributes) VALUES ('Broken', '{"color":...
                                                             ^
DETAIL:  Expected JSON value, but found "}".
CONTEXT:  JSON data, line 1: {"color": }

จากโค้ด driver ของ PostgreSQL แปลง column jsonb เป็นโครงสร้างเนทีฟ แต่ละ driver รัน query นี้หนึ่งครั้งกับสินค้าข้างบน:

-- sql/native-read.sql
SELECT attributes FROM products WHERE name = 'Trail Jacket';

const res = await pool.query(
  "SELECT attributes FROM products WHERE name = 'Trail Jacket'",
);
const attributes = res.rows[0].attributes;

cur.execute("SELECT attributes FROM products WHERE name = 'Trail Jacket'")
attributes = cur.fetchone()[0]

var attributes map[string]any
err := conn.QueryRow(ctx,
    "SELECT attributes FROM products WHERE name = 'Trail Jacket'").Scan(&attributes)

// sqlx decodes jsonb into serde_json::Value only with its "json" feature;
// without it, ask PostgreSQL for the text form
let attributes: String = sqlx::query_scalar(
    "SELECT attributes::text FROM products WHERE name = 'Trail Jacket'",
)
.fetch_one(&pool)
.await?;

                                                   attributes
----------------------------------------------------------------------------------------------------------------
 {"tags": ["outdoor", "rain"], "color": "teal", "price": 129.5, "specs": {"weight_g": 480, "waterproof": true}}
(1 row)

$ (each snippet run once against the products above)
[containment with a parameter]  Node.js (pg 8.23.0):       [ 'Trail Jacket', 'Wool Socks' ]
[containment with a parameter]  Python (psycopg 3.3.6):    ['Trail Jacket', 'Wool Socks']
[containment with a parameter]  Go (pgx v5.11.0):          [Trail Jacket Wool Socks]
[containment with a parameter]  Rust (sqlx 0.9.0):         ["Trail Jacket", "Wool Socks"]
[jsonb column read natively]    Node.js:                   object 129.5 480
[jsonb column read natively]    Python:                    dict 129.5 480
[jsonb column read natively]    Go:                        map[string]interface {} 129.5 480
[jsonb column read natively]    Rust (text via ::text):    String {"tags": ["outdoor", "rain"], "color": "teal", "price": 129.5, "specs": {"weight_g": 480, "waterproof": true}}

สี่บรรทัดท้ายแสดงการอ่านแบบเนทีฟ: Node.js, Python และ Go ให้ object, dict และ map คุณจึงใช้ attributes["price"] ได้เลย สี่บรรทัดแรกคือ query containment แบบมี parameter จากหัวข้อถัดไป (sqlx ของ Rust ต้องใช้ feature json เพื่อได้ serde_json::Value ซึ่งไม่ได้เปิดในการรันนี้ snippet จึงอ่านรูปแบบ text)

เข้าไปข้างในเอกสาร

-> คืนค่า jsonb, ->> คืน text และ #> / #>> เดินตาม path key ที่ไม่มีคืน NULL ไม่ใช่ error:

-- sql/operators.sql
SELECT name,
       attributes -> 'color'                     AS color_jsonb,
       attributes ->> 'color'                    AS color_text,
       attributes #>> '{specs,weight_g}'         AS weight,
       attributes -> 'tags' -> 0                 AS first_tag,
       attributes ->> 'missing'                  AS missing_key
FROM products
ORDER BY id;

     name      | color_jsonb | color_text | weight | first_tag | missing_key
---------------+-------------+------------+--------+-----------+-------------
 Trail Jacket  | "teal"      | teal       | 480    | "outdoor" |
 City Backpack | "black"     | black      | 900    | "urban"   |
 Camp Stove    | "silver"    | silver     | 350    | "outdoor" |
 Wool Socks    | "teal"      | teal       |        | "outdoor" |
 Rain Poncho   | "yellow"    | yellow     |        | "rain"    |
(5 rows)

เพราะ ->> ให้ text การคำนวณและการเปรียบเทียบตัวเลขจึงต้อง cast:

-- sql/numeric-cast.sql
SELECT name, (attributes ->> 'price')::numeric AS price
FROM products
WHERE (attributes ->> 'price')::numeric > 40
ORDER BY price DESC;

     name      | price
---------------+-------
 Trail Jacket  | 129.5
 City Backpack |    89
 Camp Stove    |    45
(3 rows)

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

@> ถามว่า “เอกสารซ้ายมีเอกสารขวาอยู่ข้างในไหม?” มัน match object ซ้อนและสมาชิกของ array ซึ่งเป็นเหตุที่ทำให้ index ได้ ?, ?| และ ?& ทดสอบ key:

-- sql/containment.sql
SELECT name FROM products WHERE attributes @> '{"color": "teal"}' ORDER BY id;
-- nested keys and array elements work in containment too
SELECT name FROM products WHERE attributes @> '{"specs": {"waterproof": true}, "tags": ["rain"]}' ORDER BY id;
-- key existence, any key, all keys
SELECT name FROM products WHERE attributes ? 'specs' ORDER BY id;
SELECT name FROM products WHERE attributes ?| array['specs', 'discount'] ORDER BY id;
SELECT name FROM products WHERE attributes ?& array['specs', 'tags'] ORDER BY id;

     name
--------------
 Trail Jacket
 Wool Socks
(2 rows)

     name
--------------
 Trail Jacket
 Rain Poncho
(2 rows)

     name
---------------
 Trail Jacket
 City Backpack
 Camp Stove
 Rain Poncho
(4 rows)

     name
---------------
 Trail Jacket
 City Backpack
 Camp Stove
 Rain Poncho
(4 rows)

     name
---------------
 Trail Jacket
 City Backpack
 Camp Stove
 Rain Poncho
(4 rows)

query containment แบบมี parameter จากแต่ละ driver (ส่งเอกสารเป็น JSON string และ cast ด้วย ::jsonb):

-- sql/param-containment.sql
SELECT name FROM products WHERE attributes @> $1::jsonb ORDER BY name \bind '{"color": "teal"}' \g

const res = await pool.query(
  'SELECT name FROM products WHERE attributes @> $1::jsonb ORDER BY name',
  [JSON.stringify({ color: 'teal' })],
);

cur.execute(
    "SELECT name FROM products WHERE attributes @> %s::jsonb ORDER BY name",
    (json.dumps({"color": "teal"}),),
)
rows = cur.fetchall()

rows, err := conn.Query(ctx,
    "SELECT name FROM products WHERE attributes @> $1::jsonb ORDER BY name",
    `{"color": "teal"}`)
defer rows.Close()

let rows = sqlx::query("SELECT name FROM products WHERE attributes @> $1::jsonb ORDER BY name")
    .bind(r#"{"color": "teal"}"#)
    .fetch_all(&pool)
    .await?;

     name
--------------
 Trail Jacket
 Wool Socks
(2 rows)

jsonpath (docs) เป็นภาษา query ขนาดเล็กบนเอกสาร jsonb_path_query ดึงค่า, @? ทดสอบการมีอยู่พร้อมตัวกรอง, jsonb_path_exists เป็นรูปแบบฟังก์ชัน:

-- sql/jsonpath.sql
SELECT name, jsonb_path_query(attributes, '$.specs.weight_g') AS weight_g
FROM products ORDER BY id;
SELECT name FROM products WHERE attributes @? '$.specs ? (@.weight_g < 500)' ORDER BY id;
SELECT name FROM products WHERE jsonb_path_exists(attributes, '$.tags[*] ? (@ == "cooking")') ORDER BY id;

     name      | weight_g
---------------+----------
 Trail Jacket  | 480
 City Backpack | 900
 Camp Stove    | 350
(3 rows)

     name
--------------
 Trail Jacket
 Camp Stove
(2 rows)

    name
------------
 Camp Stove
(1 row)

PostgreSQL 17 เพิ่ม JSON_TABLE ซึ่งเปลี่ยนเอกสารเป็น column เชิงสัมพันธ์ใน clause เดียว (ค่าที่ไม่มีเป็น NULL):

-- sql/json-table.sql
SELECT p.name, jt.color, jt.price, jt.weight_g
FROM products AS p,
     JSON_TABLE(p.attributes, '$' COLUMNS (
       color    text    PATH '$.color',
       price    numeric PATH '$.price',
       weight_g int     PATH '$.specs.weight_g'
     )) AS jt
ORDER BY p.id;

     name      | color  | price | weight_g
---------------+--------+-------+----------
 Trail Jacket  | teal   | 129.5 |      480
 City Backpack | black  |    89 |      900
 Camp Stove    | silver |    45 |      350
 Wool Socks    | teal   |  12.5 |
 Rain Poncho   | yellow |     9 |
(5 rows)

และเพื่อเปลี่ยนสมาชิก array เป็น row ใช้ jsonb_array_elements_text; ไปอีกทางใช้ jsonb_build_object และ jsonb_agg:

-- sql/expand-arrays.sql
SELECT tag, count(*) AS products
FROM products, jsonb_array_elements_text(attributes -> 'tags') AS tag
GROUP BY tag
ORDER BY products DESC, tag;

   tag   | products
---------+----------
 outdoor |        4
 rain    |        2
 cooking |        1
 urban   |        1
(4 rows)

-- sql/build-and-agg.sql
SELECT jsonb_build_object('total', count(*), 'names', jsonb_agg(name ORDER BY name)) AS summary
FROM products
WHERE attributes @> '{"tags": ["outdoor"]}';

                                      summary
------------------------------------------------------------------------------------
 {"names": ["Camp Stove", "Rain Poncho", "Trail Jacket", "Wool Socks"], "total": 4}
(1 row)

สองกับดัก

กับดักแรก: การเทียบผลของ -> กับสตริงเปล่า ๆ -> คืน jsonb ฝั่งขวาจึงถูก parse เป็น JSON และ teal ไม่ใช่ JSON ที่ถูกต้อง error เป็น error การ parse JSON ไม่ใช่ error เรื่องชนิด:

-- @expect-error 22P02
SELECT name FROM products WHERE attributes -> 'color' = 'teal';

ERROR:  invalid input syntax for type json
LINE 1: ...ECT name FROM products WHERE attributes -> 'color' = 'teal';
                                                                ^
DETAIL:  Token "teal" is invalid.
CONTEXT:  JSON data, line 1: teal

ทั้งสองแบบนี้ใช้ได้: เทียบกับ JSON string ('"teal"') หรือดึง text ด้วย ->> แล้วเทียบกับสตริงธรรมดา:

-- sql/equality-forms.sql
SELECT name FROM products WHERE attributes -> 'color' = '"teal"' ORDER BY id;
SELECT name FROM products WHERE attributes ->> 'color' = 'teal' ORDER BY id;

     name
--------------
 Trail Jacket
 Wool Socks
(2 rows)

     name
--------------
 Trail Jacket
 Wool Socks
(2 rows)

กับดักที่สอง: ->> คืน text และ text เทียบทีละตัวอักษร '9' > '50' เป็นจริง '129.5' > '50' เป็นเท็จ นี่คือคำตอบผิดแบบเงียบ และหัวข้อ Reading AI-generated code ด้านล่างสร้างจากมัน:

-- sql/text-comparison-trap.sql
-- ->> returns text, and text compares character by character: '9' sorts after '89'
SELECT name, attributes ->> 'price' AS price_text
FROM products
WHERE attributes ->> 'price' > '50'
ORDER BY name;

     name      | price_text
---------------+------------
 City Backpack | 89
 Rain Poncho   | 9
(2 rows)

Update บางส่วนของเอกสาร

|| รวมสองเอกสารแต่เฉพาะระดับบนสุด การตั้ง specs ด้วยวิธีนี้ แทนที่ทั้ง object specs (key waterproof หายไป) jsonb_set(doc, path, value) เปลี่ยน path ซ้อนหนึ่งจุดและเก็บ key พี่น้อง argument ที่สี่ create_missing (ค่าเริ่มต้น true) ตัดสินว่าจะสร้าง key สุดท้ายที่ไม่มีหรือไม่; - และ #- ลบ key หรือ key ซ้อน ทั้งหมดด้านล่างอยู่ใน transaction ที่ rollback:

-- sql/update-documents.sql
BEGIN;
-- || merges TOP-LEVEL keys only: the whole "specs" object is replaced
UPDATE products SET attributes = attributes || '{"specs": {"weight_g": 470}}' WHERE name = 'Trail Jacket'
RETURNING name, attributes -> 'specs' AS specs_after_concat;
ROLLBACK;
BEGIN;
-- jsonb_set changes one nested path and keeps its siblings
UPDATE products SET attributes = jsonb_set(attributes, '{specs,weight_g}', '470') WHERE name = 'Trail Jacket'
RETURNING name, attributes -> 'specs' AS specs_after_set;
-- missing path: create_missing (default true) creates the last key, false leaves the row alone
UPDATE products SET attributes = jsonb_set(attributes, '{specs,fabric}', '"nylon"', false) WHERE name = 'Trail Jacket'
RETURNING attributes -> 'specs' AS specs_create_missing_false;
-- remove a key, and a nested key
UPDATE products SET attributes = attributes - 'tags' #- '{specs,waterproof}' WHERE name = 'Trail Jacket'
RETURNING attributes;
ROLLBACK;

BEGIN
     name     | specs_after_concat
--------------+--------------------
 Trail Jacket | {"weight_g": 470}
(1 row)

UPDATE 1
ROLLBACK
BEGIN
     name     |            specs_after_set
--------------+---------------------------------------
 Trail Jacket | {"weight_g": 470, "waterproof": true}
(1 row)

UPDATE 1
      specs_create_missing_false
---------------------------------------
 {"weight_g": 470, "waterproof": true}
(1 row)

UPDATE 1
                          attributes
---------------------------------------------------------------
 {"color": "teal", "price": 129.5, "specs": {"weight_g": 470}}
(1 row)

UPDATE 1
ROLLBACK

Index สำหรับ query แบบ containment

เมื่อไม่มี index query แบบ containment อ่านทุก row นี่คือ catalog 100 000 row ที่มีเอกสารแบบเดียวกัน query ขอสินค้าสี teal ที่ติดแท็ก t7:

-- sql/catalog-setup.sql
CREATE TABLE catalog (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  attributes jsonb NOT NULL
);
INSERT INTO catalog (attributes)
SELECT jsonb_build_object(
         'color',  (ARRAY['teal', 'black', 'silver', 'yellow', 'red', 'green', 'blue', 'white'])[1 + h % 8],
         'price',  5 + (h / 8) % 200,
         'tags',   jsonb_build_array('t' || (h / 1600) % 60, 't' || (h / 96000) % 97),
         'specs',  jsonb_build_object('weight_g', 100 + (h / 7) % 900))
FROM (SELECT ('x' || substr(md5(g::text), 1, 7))::bit(28)::int AS h FROM generate_series(1, 100000) g) s;
ANALYZE catalog;
SELECT count(*) AS rows, pg_size_pretty(pg_relation_size('catalog')) AS heap FROM catalog;

CREATE TABLE
INSERT 0 100000
ANALYZE
  rows  | heap
--------+-------
 100000 | 15 MB
(1 row)

-- sql/catalog-no-index.sql
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF)
SELECT count(*) FROM catalog WHERE attributes @> '{"color": "teal", "tags": ["t7"]}';

                                 QUERY PLAN
----------------------------------------------------------------------------
 Aggregate (actual time=15.556..15.561 rows=1.00 loops=1)
   ->  Seq Scan on catalog (actual time=0.133..15.531 rows=330.00 loops=1)
         Filter: (attributes @> '{"tags": ["t7"], "color": "teal"}'::jsonb)
         Rows Removed by Filter: 99670
 Planning Time: 0.215 ms
 Execution Time: 15.599 ms
(6 rows)

GIN index แบบ jsonb_path_ops (เล็กกว่าและเร็วกว่าสำหรับ @>; class jsonb_ops เริ่มต้นรองรับ ? ด้วย) เปลี่ยนมันเป็นการค้นหาผ่าน index:

-- sql/catalog-gin.sql
CREATE INDEX catalog_attr_gin ON catalog USING gin (attributes jsonb_path_ops);
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF)
SELECT count(*) FROM catalog WHERE attributes @> '{"color": "teal", "tags": ["t7"]}';
SELECT pg_size_pretty(pg_relation_size('catalog_attr_gin')) AS gin_size;

CREATE INDEX
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Aggregate (actual time=0.729..0.733 rows=1.00 loops=1)
   ->  Bitmap Heap Scan on catalog (actual time=0.268..0.716 rows=330.00 loops=1)
         Recheck Cond: (attributes @> '{"tags": ["t7"], "color": "teal"}'::jsonb)
         Heap Blocks: exact=305
         ->  Bitmap Index Scan on catalog_attr_gin (actual time=0.234..0.234 rows=330.00 loops=1)
               Index Cond: (attributes @> '{"tags": ["t7"], "color": "teal"}'::jsonb)
               Index Searches: 1
 Planning Time: 0.261 ms
 Execution Time: 0.775 ms
(9 rows)

 gin_size
----------
 1664 kB
(1 row)

16 ms กลายเป็น 0.8 ms สำหรับ 330 row ที่ตรง โดยแลกกับ index 1.7 MB บน table 15 MB index รองรับเฉพาะ operator บน column เอง (@>, ?, @?): expression ที่ดึงออกมาเช่น attributes->>'color' ต้องมี B-tree expression index ของตัวเอง บท index เฉพาะทางเทียบ GIN operator class อย่างละเอียด

เอกสารใหญ่และ TOAST

row ที่ใหญ่กว่าราว 2 kB จะถูกย้ายค่าใหญ่ออกไปไว้ใน TOAST table (และบีบอัดถ้าช่วยได้) เอกสาร jsonb ถูกเก็บเป็นค่าเดียว การเปลี่ยน key เล็ก ๆ หนึ่งตัวจึงเขียนเอกสารใหม่ทั้งฉบับ เอกสาร 2 000 ฉบับขนาดราว 9.6 kB (ข้อความสุ่มซึ่งบีบอัดไม่ได้):

-- sql/toast.sql
CREATE TABLE docs (id int PRIMARY KEY, body jsonb NOT NULL);
INSERT INTO docs
SELECT g, jsonb_build_object('id', g, 'note', 'first', 'blob', (SELECT string_agg(md5(random()::text), '') FROM generate_series(1, 300)))
FROM generate_series(1, 2000) g;
ANALYZE docs;
SELECT pg_column_size(body) AS stored_bytes, octet_length(body::text) AS as_text_bytes FROM docs WHERE id = 1;
SELECT pg_size_pretty(pg_relation_size('docs')) AS heap,
       pg_size_pretty(pg_relation_size((SELECT reltoastrelid FROM pg_class WHERE relname = 'docs'))) AS toast;
-- change ONE small key in every document
UPDATE docs SET body = jsonb_set(body, '{note}', '"second"');
SELECT pg_size_pretty(pg_relation_size((SELECT reltoastrelid FROM pg_class WHERE relname = 'docs'))) AS toast_after_small_update;

CREATE TABLE
INSERT 0 2000
ANALYZE
 stored_bytes | as_text_bytes
--------------+---------------
         9653 |          9638
(1 row)

  heap  | toast
--------+-------
 104 kB | 20 MB
(1 row)

UPDATE 2000
 toast_after_small_update
--------------------------
 39 MB
(1 row)

heap มี 104 kB และเอกสารอยู่ใน TOAST (20 MB) การเปลี่ยน key เล็ก note ในทุกเอกสารทำให้ TOAST table โตเป็น 39 MB: แต่ละ update เขียนสำเนาเต็มของเอกสารใหม่ และสำเนาเก่ารอ VACUUM ถ้าคุณ update field เดียวของเอกสารใหญ่บ่อย field นั้นควรเป็น column ของตัวเอง

Reading AI-generated code

ผู้ช่วย AI ถูกขอ “สินค้าที่แพงกว่า 100” จากราคาใน jsonb และเขียน query ที่ตรงไปตรงมา:

🐛 หา bug ในโค้ดนี้

-- AI comment: "products over 100"
SELECT name, attributes ->> 'price' AS price
FROM products
WHERE attributes ->> 'price' > '100'
ORDER BY name;

เฉลย

ผลดูสมเหตุสมผลและผิด ->> ให้ text และ text เรียงทีละตัวอักษร '9', '12.5', '45' และ '89' จึง “มากกว่า” '100' ทั้งหมด บนข้อมูลนี้ query คืน ทุก สินค้า รวมถึงตัวที่ราคา 9 และ 12.5 และจะทิ้งหรือรวมตัวที่ผิดเมื่อราคาเปลี่ยน:

-- sql/stb-wrong.sql
SELECT name, attributes ->> 'price' AS price
FROM products
WHERE attributes ->> 'price' > '100'
ORDER BY name;

     name      | price
---------------+-------
 Camp Stove    | 45
 City Backpack | 89
 Rain Poncho   | 9
 Trail Jacket  | 129.5
 Wool Socks    | 12.5
(5 rows)

cast เป็น numeric ก่อนเทียบ (และถ้าตัวกรองนี้สำคัญ ให้ index expression ((attributes->>'price')::numeric)):

-- sql/stb-fixed.sql
SELECT name, attributes ->> 'price' AS price
FROM products
WHERE (attributes ->> 'price')::numeric > 100
ORDER BY name;

     name     | price
--------------+-------
 Trail Jacket | 129.5
(1 row)

Mental Model

jsonb เป็นชนิดค่าที่บังเอิญมี key คุณดึงจากมันได้ (->, ->>, jsonpath) ทดสอบได้ (@>, ?) สร้างใหม่ได้ (||, jsonb_set, -) และคลายเป็น row ได้ (jsonb_each, jsonb_array_elements, JSON_TABLE) การดึงให้ jsonb หรือ text: เลือกอย่างตั้งใจ และ cast ก่อนเทียบตัวเลขหรือวันที่ การเก็บคือหนึ่งค่าทึบต่อ row: GIN index ทำให้ containment เร็ว และทุกการเขียนไปยัง key ใดก็เขียนทั้งค่าใหม่

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

ใช้ jsonb ไม่ใช่ json เว้นแต่ต้องรักษาข้อความเดิมเป๊ะ
-> คืน jsonb, ->> คืน text; cast (::numeric, ::date) ก่อนเทียบตัวเลขหรือวันที่
ใช้ @> สำหรับตัวกรองที่ต้องการ index; attributes->>'k' = 'v' ต้องใช้ expression index แทน
|| เป็น shallow merge; ใช้ jsonb_set สำหรับการเปลี่ยนแบบซ้อน
column ที่คุณ filter, join หรือ update บ่อย ควรมี column จริงของตัวเอง เก็บ jsonb ไว้สำหรับส่วนที่ยืดหยุ่นจริง ๆ
driver ต่างกัน: pg parse เป็น object, psycopg ให้ dict, pgx ให้ map[string]any, sqlx ต้องมี feature flag

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

ตัวเลือก	Benefit	Cost
column `jsonb`	รูปร่างยืดหยุ่น ไม่ต้อง migration ต่อ attribute index ด้วย GIN ได้	ไม่ตรวจชนิดของ key; การเปลี่ยนเล็ก ๆ เขียนเอกสารใหม่ทั้งฉบับ (TOAST 20 MB เป็น 39 MB ข้างบน)
column จริง	ชนิด, constraint, foreign key, update ถูก	ต้องเปลี่ยน schema (migration) ต่อ attribute ใหม่
`json` (text)	รักษาข้อความเดิม	parse ใหม่ทุกครั้งที่เข้าถึง index ไม่ได้
GIN `jsonb_path_ops`	16 ms เหลือ 0.8 ms สำหรับ `@>`	1.7 MB ที่นี่; ไม่รองรับ `?`

Common Misconceptions

“-> คืน text” มันคืน jsonb; การเทียบกับ 'teal' เป็น error การ parse JSON ->> ต่างหากที่คืน text
“jsonb เก็บลำดับ key และค่าซ้ำของฉัน” มันไม่เก็บทั้งสอง: {"a": 3, "b": 1}
“|| รวม object ซ้อนกัน” มันแทนที่ทั้ง object ซ้อน: specs เสีย waterproof
“การเทียบค่า ->> เป็นตัวเลขใช้ได้” มันเทียบ text: '9' > '50'
“GIN index เร่งทุกตัวกรองบน column” เฉพาะ operator containment/existence บน column ไม่ใช่ expression ->>