JSON in Postgres

[xluffy]

JSON

PostgreSQL giới thiệu kiểu dữ liệu json kể từ Postgres 9.2, mục tiêu là làm đa dạng hệ sinh thái, cung cấp thêm tính năng làm đối trọng với NoSQL. Tuy nhiên ở phiên bản này json đơn giản chỉ là một dạng text field. Tức là không có nhiều tính năng hữu ích lắm. Ví dụ ta có thể extract dữ liệu dựa trên key của object, tuy nhiên các operation này sẽ rất chậm và ko optimized cho JSON object lớn.

Một vấn đề khác là do bản chất vẫn là text, nên dữ liệu lưu dạng raw, tức là Postgres sẽ giữ nguyên hiện trạng của dữ liệu được input vào, như key-orders, whitespace hoặc duplicate keys trong object (lưu whitespace thì rất tốn dung lượng) -> lí do là Postgres muốn enforce data là valid JSON

=> Nói tóm lại, tại thời điểm version 9.2, json chủ yếu được giới thiệu cho có chứ về mặt hữu ích thì chưa mang nhiều giá trị

JSONB

Tới phiên bản Postgres 9.4, PostgreSQL giới thiệu kiểu dữ liệu jsonb, đây mới có thể coi là json-real, chữ b trong kiểu dữ liệu này có nghĩa là better, điều này là do jsonb lưu trữ JSON data dưới dạng special-binary, với định dạng này thì dữ liệu được nén và lưu hiệu quả hơn so với text.

Với việc sử dụng optimized format, cho phép PostgreSQL support nhiều operation mới hơn ví dụ việc extract value từ một key của object sẽ siêu nhanh, ngoài ra jsonb còn cho phép:

• Set a new key
• Update the value of an existing key
• Set a value in a nested object
• Update the value of a nested key
• Delete a key
• Delete a nested key
• Concatenate JSON objects
• Deal with JSON array

Chi tiết các build-in jsonb function được PostgreSQL cung cấp có thể xem ở link

Main differences

Sự khác biệt lớn nhất giữa json và jsonb là cách chúng được lưu trữ ở phía internal PostgreSQL.

Dữ liệujson sẽ được lưu trữ chính xác với dữ liệu đầu vào, nên mỗi function/operation sẽ phải reparse toàn bộ dữ liệu mỗi lần thực thi. Nên phần query dữ liệu sẽ tốn nhiều chi phí hơn, nhưng bù lại phần ghi dữ liệu sẽ ko cần xử lí gì nhiều.

Ngược lại, jsonb sẽ lưu dữ liệu ở dạng nén/binary nên INSERT operation sẽ hơi chậm một xíu, nhưng ở phần query sẽ không cần phải reparse nên sẽ nhanh hơn so với json, và tất nhiên jsonb cũng hỗ trợ nhiều function/operation hơn và cũng support indexing tốt hơn so với json -> nên sẽ cải thiện hiệu suất khi parsing và query JSON data.

Tới phiên bản hiện tại là Postgres 16 thì khả năng là json không còn nhiều ứng dụng, tuy nhiên về lí thuyết json sẽ hữu ích với các use-case:

• Chủ yếu INSERT
• Dữ liệu có sao, lấy lên y chang vậy, không xử lí gì
• Dữ nguyên vẹn dữ liệu đầu vào.

Better of better JSON with PostgreSQL 14

Ở phiên bản Postgres 13, ví dụ với một query như sau:

SELECT *
FROM shirts
WHERE details->'attributes'->>'color' = 'neon yellow'
  AND details->'attributes'->>'size' = 'medium';

Ta sẽ có 2 operation ở đây là -> và ->> với

• -> là duyệt qua JSON docs
• ->> là extract value dưới dạng text

Từ Postgres 14, 2 operation trên có thể sử dụng dưới dạng quotation-mark (như các ngôn ngữ lập trình, JSON-style) như sau:

SELECT *
FROM shirts
WHERE details['attributes']['color'] = '"neon yellow"'
  AND details['attributes']['size'] = '"medium"'

hoặc UPDATE

UPDATE shirts
SET details['attributes']['color'] = '"neon blue"'
WHERE id = 123;

Indexing:

• B-Tree index
• Hash index
• GIN (GIN, GIN (jsonb_path_ops), GIN(gin_trgm_ops))

Link:

• https://www.crunchydata.com/blog/using-postgresql-for-json-storage
• JSON function/operation: https://www.postgresql.org/docs/16/functions-json.html
• https://www.crunchydata.com/blog/better-json-in-postgres-with-postgresql-14