[postgresql] 在 PostgreSQL 如何 query JSONB

最近第一次碰到 PostgreSQL 的 JSONB 欄位，紀錄一下使用情境和如何 query JSONB。

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.

在 PostgreSQL 9.4 開始有 JSON 資料儲存型態，官方建議在大部分的應用場景，要儲存 JSON 資料時應該使用 JSONB (ref.)

看到這裡你一定會想，那我們是不是可以都開一個 JSONB 欄位來存所有的資料呢？🤔

其實不然，大多數的資料應用是可以正規化出欄位與型態以至關聯，如果使用 JSONB 來存放資料，反而失去一些 PostgreSQL 的優化，例如索引、資料型態檢查等至於資料新增、修改和搜尋的效能也會相對沒這麼好，而且更像在使用 NoSQL 資料庫，那究竟在什麼情境下使用 JSONB 會比較合適？

幾個適合使用 JSONB 欄位的情境：

Change logs: 紀錄資料物件的變更，像是一筆資料的 snapshot
3rd api response(webhook): 第三方的 api 回傳格式沒辦法保證穩定的欄位格式
Elasticsearch/Algolia query: 多樣性且不固定格式的 payload

Note: 可以看出以上適合的情境都是類似 EAV (Entity-Attribute-Value) 的資料模型，比較難以對資料正規化出固定的欄位，這時候使用 JSONB 欄位相對的會更適合。

如果覺得不是很好判斷使用情境是否適合使用 JSONB 欄位，建議使用前可以先思考以下幾點：

可以容易的將資料正規化出固定的欄位與型態，不應該使用 JSONB 欄位
預期在每次更新資料任一 key/value 時都會更新整個 JSONB 欄位
時常需要對資料的 key/value 做 where 和 join，JSONB 欄位可能不是好選擇
你的 JSONB 資料中有 foreign key 的需求，不應該使用 JSONB 欄位
你期望 PostgreSQL 可以幫你做欄位中的資料型態檢查，不應該使用 JSONB 欄位

如何 query JSONB 欄位

下面列出常用的 query 範例，假設我們有一個 snapshot 的 JSONB 欄位，裡面存放了一筆資料的 snapshot，我們要如何 query 這個 JSONB 欄位？

{
  "name": "Example Product",
  "price": 19.99,
  "currency": "USD",
  "category": "Electronics",
  "reviews": [
    {
      "user": "User1",
      "comment": "Great product!",
      "rating": 5
    },
    {
      "user": "User2",
      "comment": "Good value for money.",
      "rating": 4
    }
  ]
}

右側的 key/value pair 是否存在 top-level object 中

SELECT * FROM products WHERE snapshot @> '{"name": "Example Product"}';
SELECT * FROM products WHERE snapshot @> '{reviews: [ {user: "User1"} ]}';

Query 指定的 key/value

SELECT * FROM products WHERE snapshot ->> 'name' = 'Example Product';
SELECT * FROM products WHERE snapshot -> 'reviews' ->> 'user' = 'User1';
SELECT * FROM products WHERE snapshot -> 'reviews' @> '[{"user": "User1"}]';

檢查 key 是否存在

SELECT * FROM products WHERE snapshot ? 'name';
SELECT * FROM products WHERE snapshot -> 'name' IS NOT NULL;

Query Array by index

SELECT * FROM products WHERE snapshot #> '{reviews,0}' @> '{"user": "User1"}';

檢查 Array 長度 > 0

SELECT * FROM products WHERE jsonb_array_length(snapshot -> 'reviews') > 0;

[postgresql] 在 PostgreSQL 如何 query JSONB

如何 query JSONB 欄位

參考資料