Menu

JSONB trong PostgreSQL: toán tử, truy vấn và index

Lưu và truy vấn JSON trong PostgreSQL với jsonb: toán tử -> và ->>, phép chứa @>, jsonb_set và các cách cập nhật khác, biến mảng thành hàng, index GIN, và json so với jsonb.

Trang này có trình soạn thảo chạy trực tiếp. Sửa, chạy và xem đầu ra ngay lập tức.

jsonb là kiểu cột JSON dạng nhị phân của PostgreSQL. Đọc trường bằng -> (trả về jsonb) và ->> (trả về text), tìm hàng bằng toán tử chứa @>, và sửa trường bằng jsonb_set hoặc ||. Một index GIN giúp các truy vấn này chạy nhanh.

Đơn hàng 1 và 3 khớp. data->'customer' trả về đối tượng lồng nhau dạng jsonb, ->>'name' lấy tên ra dưới dạng văn bản, còn ->> cộng với một phép ép kiểu biến total thành số.

Lấy giá trị ra khỏi JSONB

Toán tửTrả vềVí dụ
-> khóa hoặc chỉ sốjsonbdata->'customer', data->'tags'->0
->> khóa hoặc chỉ sốtextdata->>'status'
#> đường dẫnjsonbdata#>'{customer,city}'
#>> đường dẫntextdata#>>'{customer,city}'
[...] subscript (PostgreSQL 14+)jsonbdata['customer']['city']

Chỉ số mảng bắt đầu từ 0, và chỉ số âm đếm từ cuối (-> -1 là phần tử cuối cùng). Khóa không tồn tại cho ra SQL NULL, không phải lỗi, nên đơn hàng 3 ở trên có city là NULL.

Dùng ->> để so sánh với văn bản hoặc để ép kiểu: data->>'status' = 'paid'. Dùng -> để đi tiếp vào bên trong. Một lỗi hay gặp là data->'status' = 'paid'. PostgreSQL đọc 'paid' như JSON, một từ không có dấu nháy không phải JSON hợp lệ, và truy vấn báo lỗi "invalid input syntax for type json".

Tìm hàng theo nội dung JSON

@> kiểm tra giá trị bên trái có chứa giá trị bên phải không. Nó hoạt động ở mọi độ sâu và bên trong mảng, và được index GIN hỗ trợ:

Sản phẩm 1 và 2 có size M và có giá; chỉ sản phẩm 2 đắt hơn 30. Các toán tử tìm kiếm:

  • @> chứa, <@ nằm trong.
  • ? khóa (hoặc chuỗi trong mảng) tồn tại, ?| có bất kỳ khóa nào tồn tại, ?& tất cả các khóa đều tồn tại.
  • @? và @@ chạy truy vấn JSON path (PostgreSQL 12+), để so sánh bên trong tài liệu.

Cập nhật trường bên trong JSONB

PostgreSQL ghi lại toàn bộ giá trị ở mỗi lần cập nhật, nhưng các hàm sau cho phép bạn chỉ mô tả phần thay đổi:

  • jsonb_set(target, path, new_value) thay một giá trị tại một đường dẫn.
  • - xóa một khóa, và #- xóa một đường dẫn lồng nhau như '{notify,sms}'.
  • || thêm hoặc ghi đè các khóa cấp cao nhất.

Từ PostgreSQL 14, bạn cũng có thể gán qua subscript: SET prefs['notify']['email'] = 'false'.

Hai cái bẫy:

  • jsonb_set trả về NULL khi giá trị mới là SQL NULL, và khi đó UPDATE xóa sạch cả cột. Hãy truyền COALESCE(to_jsonb(x), 'null'), hoặc dùng jsonb_set_lax (PostgreSQL 13+), hàm này mặc định lưu JSON null.
  • jsonb_set thêm khóa cuối nếu thiếu, nhưng không thêm các cấp cha bị thiếu: đặt '{a,b}' trên {} sẽ không thay đổi gì. Phép gán qua subscript thì có tạo ra chúng.

Biến mảng JSON thành hàng

jsonb_array_elements trả về một hàng cho mỗi phần tử mảng, còn jsonb_to_recordset biến một mảng đối tượng thành các cột có kiểu. Từ PostgreSQL 17, JSON_TABLE theo chuẩn SQL làm được cả hai việc ở một chỗ:

A-1 bán được 3 và B-2 bán được 1. Theo chiều ngược lại, jsonb_build_object('id', id, 'name', name) tạo một đối tượng từ các cột và jsonb_agg gom các hàng thành một mảng (xem string_agg và array_agg).

Tạo index GIN cho JSONB

Một index GIN trên cả cột phục vụ @>, ?, ?|, ?&, @? và @@:

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'));

Viết truy vấn theo dạng mà index hỗ trợ. WHERE data @> '{"status": "paid"}' dùng được index GIN. WHERE data->>'status' = 'paid' thì không; nó cần index trên biểu thức. Một phép so sánh khoảng như (data->>'total')::numeric > 50 cần index trên đúng biểu thức đó.

JSON và JSONB khác nhau thế nào

jsonjsonb
Lưu dưới dạngVăn bản đầu vàoDạng nhị phân đã phân tích
Khoảng trắng, thứ tự khóa, khóa trùngGiữ nguyênBị bỏ; khóa trùng cuối cùng được giữ
GhiNhanh hơn một chút (chỉ kiểm tra hợp lệ)Phân tích khi nhập
Đọc và truy vấnPhân tích lại ở mỗi lần truy cậpNhanh
@>, ? và index GINKhôngCó

json trả về văn bản y nguyên; jsonb chỉ giữ "a": 3 và sắp xếp lại các khóa. Hãy dùng jsonb, trừ khi bạn phải trả về đúng từng byte văn bản gốc, ví dụ payload webhook đã lưu. Giữ các trường mà hàng nào cũng có, hoặc cần khóa ngoại, trong cột bình thường. Tài liệu tham khảo chính thức về hàm JSON liệt kê mọi toán tử và hàm.

Câu hỏi thường gặp

-> và ->> trong PostgreSQL khác nhau thế nào?

-> trả về giá trị dạng jsonb, nên bạn có thể đi tiếp vào bên trong (data->'customer'->'city'). ->> trả về text, là dạng bạn dùng để so sánh với chuỗi hoặc ép sang số: (data->>'total')::numeric.

Làm sao kiểm tra một mảng JSONB có chứa một giá trị?

Dùng phép chứa: data->'tags' @> '["gift"]', hoặc data @> '{"tags": ["gift"]}' để kiểm tra từ gốc tài liệu. Với mảng chuỗi, data->'tags' ? 'gift' cũng được. Một index GIN trên data giúp cách thứ hai chạy nhanh hơn.

JSONB có giữ thứ tự các khóa không?

Không. jsonb lưu khóa theo thứ tự riêng của nó, bỏ khoảng trắng và chỉ giữ khóa cuối cùng khi bị trùng. Nếu cần lấy lại đúng văn bản gốc, hãy dùng kiểu json.

Làm sao kiểm tra một khóa có tồn tại trong JSONB?

data ? 'key' đúng khi đối tượng cấp cao nhất có khóa đó, kể cả khi giá trị của nó là JSON null. ?| nhận một mảng và đúng nếu có bất kỳ khóa nào tồn tại, ?& đúng nếu tất cả đều tồn tại.

Có thể thêm khóa ngoại hoặc ràng buộc NOT NULL cho một trường bên trong JSONB không?

Không trực tiếp. Ràng buộc CHECK có thể kiểm tra một trường (CHECK (data ? 'email')), nhưng khóa ngoại cần một cột thật. Những trường mà hàng nào cũng có và bảng khác tham chiếu tới nên nằm trong cột bình thường.

Hình minh họa các ngôn ngữ lập trình của Coddy

Học lập trình cùng Coddy

Bắt đầu