JSON và ARRAY trong PostgreSQL

1212121

I. JSON trong 9.2

Kiểu dữ liệu JSON xuất hiện lần đầu trong PostgreSQL 9.2 về cơ bản là một cột text được gắn cờ như dữ liệu JSON để xử lý thông qua một parser. Trong 9.2, bạn có thể chuyển các dòng và mảng trong json và cho mọi thứ khác bằng cách sử dụng một trong các ngôn ngữ PL. Nó hữu ích trong một vài trường hợp nhưng vẫn cần nhiều hơn. Để minh họa, chúng ta tạo ra một bảng:

CREATE TABLE filmsjson (id BIGSERIAL PRIMARY KEY, data JSON);
INSERT INTO filmsjson (data)
VALUES (‘{
“title”: “The Shawshank Redemption”,
“num_votes”: 1566874,
“rating”: 9.3,
“year”: “1994”,
“type”: “feature”,
“can_rate”: true,
“tconst”: “tt0111161”,
“image”: {
“url”: “http://sample.com/images/M/MV5BO.jpg “,
“width”: 933,
“height”: 1388
}
}’);

SELECT * FROM filmsjson;

Ngoài việc lưu trữ và nhận toàn bộ tài liệu, có rất ít thứ chúng ta có thể làm với nó. Chú ý, tất cả khoảng trắng vẫn được giữ nguyên. Cái sẽ quan trọng sau này…

Nhanh chóng chuyển tới PostgreSQL 9.3

Đằng sau parser mới cho JSON trong PostgreSQL 9.3, các toán tử xuất hiện để trích xuất các giá trị từ kiểu dữ liệu JSON, bao gồm ->, ->>, #> và #>>

SELECT data->’title’ FROM filmsjson;
SELECT data#>'{image,width}’ FROM filmsjson;

SELECT data#>ARRAY[‘image’,’width’] FROM filmsjson;

Mặc dù kết hợp một tập hợp các hàm nhưng vẫn còn khá nhiều hạn chế. Nó không thực sự cho phép thực hiện các truy vấn phức tạp, việc đánh chỉ mục giới hạn trên các trường cụ thể và chỉ có một vài cách để tạo ra các phần tử JSON. Nhưng quan trọng nhất là việc parsing một trường text là không hiệu quả.

PostgreSQL 9.4

PostgreSQL 9.4 giới thiệu JSONB. JSONB là một phiên bản mã hóa binary của JSON nó lưu trữ các key và giá trị của tài liệu JSON hiệu quả hơn. Điều này có nghĩa là tất cả các các khoảng trắng sẽ được bỏ đi và cùng với nó là tất cả sự cần thiết phải phân tích cú pháp JSON. Bạn cũng không thể có các keys giống nhau cùng cấp độ và bạn thường mất định dạng cấu trúc tài liệu. Đó là một sự hi sinh có giá trị bởi vì tất cả mọi thứ nói chung hiệu quả hơn vì không cần phải parsing text. Nó cũng làm insert dữ liệu chậm đi bởi vì các phân tích thực sự được thực hiện. Để thấy sự khác biệt, hãy tạo một bảng JSONB và chèn dữ liệu vào nó.

CREATE TABLE filmsjsonb (id BIGSERIAL PRIMARY KEY, data JSONB);
INSERT INTO filmsjsonb (data)
VALUES (‘{
“title”: “The Shawshank Redemption”,
“num_votes”: 1566874,
“rating”: 9.3,
“year”: “1994”,
“type”: “feature”,
“can_rate”: true,
“tconst”: “tt0111161”,
“image”: {
“url”: “http://sample.com/images/M/MV5BO.jpg “,
“width”: 933,
“height”: 1388
}
}’);

SELECT * FROM filmsjsonb;

Bạn có thể thấy các khoảng trắng bị bỏ đi để lại danh sách key/value ngắn gọn.

Mặc dù chia sẻ nhiều tính năng, nhưng đây là phần khá thú vị: JSONB không có hàm tạo. Trong 9.4, kiểu dữ liệu JSONB là tập hợp các hàm tạo mở rộng: json_build_object(), json_build_array() và json_object(). Các hàm này được sử dụng để tạo thành phiên bản JSONB. Nó phản ánh logic các lập trình viên PostgreSQL đã áp dụng: JSON cho việc lưu trữ tài liệu, JSONB nhanh, thao tác hiệu quả. Vì trong khi JSON và JSONB đều có các toán tử ->, ->>, #> và #>> , chỉ JSONB có toán tử  kiểm tra “exists” bao gồm ?, ?|, ?& và “contains” bao gồm @>, <@. Ví dụ chúng ta có thể kiểm tra key "rating" có tồn tại trong trường "data":

SELECT data->’title’ FROM filmsjsonb WHERE data ? ‘rating’;
SELECT data->’title’ FROM filmsjsonb WHERE data->’image’ ? ‘url’;

Bây giờ, chúng ta muốn các movie trong năm 1972, chúng ta có thể  tìm kiếm các bản ghi chứa “year”:”1972″:

SELECT data->’title’ FROM filmsjsonb WHERE data @> ‘{“year”:”1972″}’;
SELECT data->’title’ FROM filmsjsonb WHERE data @> ‘{ “image”:{“width”:500}}’;

Mặc dù trong 9.4 PostgreSQL bạn có thể tạo, trích xuất và đánh chỉ mục JSON/JSONB. Cái còn thiếu là khả năng chỉnh sửa kiểu dữ liệu JSON/JSONB. Bạn cần truyền dữ liệu JSON tới một ngôn ngữ kịch bản PLv8 hoặc PLPerl nơi chúng ta có thể chỉnh sửa JSON. Vì thế 9.4 chưa hẳn là môi trường xử lý tài liệu JSON với đầy đủ các dịch vụ.

Đi vào PostgreSQL 9.5

Những tính năng mới của PostgreSQL 9.5 là khả năng sửa đổi và thao tác dữ liệu JSONB. Một phần trong số chúng là hàm jsonb_pretty() làm cho JSONB dễ đọc hơn, hãy bắt đầu với câu truy vấn:

SELECT data FROM filmsjsonb WHERE id=1;
SELECT jsonb_pretty(data) FROM filmsjsonb WHERE id=1;

Kết quả câu truy vấn dễ dọc hơn.

Thao tác delete

Delete là các chỉnh sửa đơn giản nhất. Chỉ cần nói cái bạn muốn bỏ đi và thực hiện. PostgreSQL 9.5 giới thiệu các toán tử – và #- . Vì thế, với cơ sở dữ liệu lưu trữ movie, nếu chúng ta muốn xóa trường “rating” đây có thể là cách:

UPDATE filmsjsonb
SET data=data-‘rating’;

Toán tử  #- làm được nhiều hơn, nó nhận một đường dẫn như một tham số. Nếu chúng ta muốn xóa các thuộc tính kích thước của hình ảnh:

UPDATE filmsjsonb
SET data=data#-‘{image,width}’;
UPDATE filmsjsonb
SET data=data#-‘{image,height}’;
SELECT jsonb_pretty(data) FROM filmsjsonb WHERE id=1;

Chúng ta viết 2 câu lệnh update vì đường dẫn cụ thể không cho phép các key tùy chọn, nhưng chúng ta có thể giảm xuống một câu lệnh update bằng biểu thức phức tạp hơn:

UPDATE filmsjsonb
SET data#-‘{image,height}’#-‘{image,width}’;

Hoặc chỉ cần xóa nó từ kết quả:

SELECT jsonb_pretty(data#-‘{image,height}’#-‘{image,width}’)
FROM filmsjsonb
WHERE id=1;

Toán tử add

Ví dụ, chúng ta cần thiết lập giá trị của trường “can_rate” thành false, xóa trường “num_votes” và thêm một trường mới “revote” có giá trị là true:

UPDATE filmsjsonb
SET data = data || ‘{“can_rate”:false,”num_votes”:0,”revote”:true }’;
SELECT jsonb_pretty(data) FROM filmsjsonb WHERE id=1;

jsonb_set for success

Hàm “jsonb_set()” được thiết kế để cập nhật một trường ở bất kỳ đâu trong tài liệu JSON. Ví dụ:

UPDATE filmsjsonb
SET data = jsonb_set(data,'{“image”,”width”}’,to_jsonb(1024))
WHERE id=1;

Câu lệnh trên sẽ thay đổi giá trị của thuộc tính image.width thành 1024. Các tham số của hàm “jsonb_set()” khá đơn giản: tham số đầu tiên là trường có kiểu dữ liệu là JSONB bạn muốn chỉnh sửa, tham số thứ 2 là đường dẫn và tham số thứ 3 là giá trị thay thế giá trị hiện tại của thuộc tính ở cuối đường dẫn. Nếu cặp key/value ở cuối đường dẫn chưa tồn tại, mặc định hàm “jsonb_set()” sẽ tạo và thiết lập giá trị cho nó. Ví dụ chúng ta muốn thêm một đối tượng mới cho trường “data” chỉ cần làm:

UPDATE filmsjsonb
SET data = jsonb_set(data,'{“image”,”quality”}’,'{“copyright”:”company X”,”registered”:true}’)
WHERE id=1;
SELECT jsonb_pretty(data) FROM filmsjsonb WHERE id=1;

“jsonb_set()” có lẽ là sự bổ sung quan trọng nhất trong các hàm JSON của Postgres 9.5.

II. Sử dụng Array trong Postgres để thay thế cho bảng trung gian

Thông thường, khi sử dung đến các quan hệ 1:nhiều hoặc nhiều:nhiều trong bảng quan hệ để có thể truy vấn ta cần có bảng trung gian ở giữa. Với việc sử dụng kiểu Array trong Postgres bạn có thể loại bỏ được bảng trung gian này.

Bạn có 2 bảng như sau

  1. Users
    • id – serial – Primary Key
    • username – character varying[255]
    • email – character varying[255]
    • jobs – integer[] << Array

Screen 1

  1. Jobs
    • id – serial – Primary Key
    • name – character varying[255]
    • description – character varying[255]

Screen 2

Để có thể kết nối dữ liệu giữa 2 bảng :

SELECT users.* , array_to_json(array_agg(jobs.*)) as detail_jobs
FROM users LEFT JOIN jobs ON jobs.id = ANY(users.jobs)
GROUP BY users.id;

Kết quả bạn nhận được:

Screen 3

Ta có thể chọn định danh tên côt trong 1 mảng trả về ví dụ:

SELECT users.* , array_to_json(array_agg(jobs.name)) as detail_jobs
FROM users LEFT JOIN jobs ON jobs.id = ANY(users.jobs)
GROUP BY users.id;

Output:

Screen 4

Thay vì sử dụng ANY bạn có thể sử dụng cú pháp IN và unnest

SELECT users.* , array_to_json(array_agg(jobs.*)) as detail_jobs
FROM users LEFT JOIN jobs ON jobs.id IN (SELECT unnest(users.jobs))
GROUP BY users.id;

Bạn có thể thêm giá trị vào trong Array bằng cú pháp

WITH u AS (
SELECT array_append(users.jobs, 3) AS jobs
FROM users
WHERE id = 1
)
UPDATE users
SET jobs = u.jobs
FROM u
WHERE users.id = 1;

Bạn cũng có thể xóa 1 jobs khỏi Array bằng cú pháp

WITH new_jobs AS (
SELECT array_agg(jobs.job) AS jobs
FROM (
SELECT unnest(users.jobs) AS job
FROM users
WHERE id = 1
)jobs
WHERE jobs.job != 3
)
UPDATE users
SET jobs = new_jobs.jobs
FROM new_jobs
WHERE id = 1;

Link bài viết gốc:
https://www.compose.com/articles/could-postgresql-9-5-be-your-next-json-database/
http://blog.j0.hn/post/57492309635/using-arrays-as-relation-tables-in-postgres

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s