跳至主要内容

Jsonb 內容的 where 查詢

紀錄能與 GIN index (Generalized Inverted Index) 相互搭配使用的 查詢類 Operators。
這幾個 operators 主要 用在 WHERE 子句 中，回傳結果都是 boolean。
在 index 搭配下能達到更高的查詢效率。

  • 詞彙註解:
    • 頂層鍵(top-level key): 指的是 Json 物件中未槽串 (nested) 的屬性鍵值。也就是第一層的 keys 。

  • Jsonb 查詢類 Operators: 可以想成是 Jsonb 中的 where conditions
    •  @> : 左側 Jsonb (資料)是否包含右側 Jsonb (條件)內容。
    •  ?| : Jsonb Object 是否包含指定的 任一 keys (String Array)。
    •  ?& : Jsonb Object 是否包含指定的 全部 keys (String Array)。
    • ? : Jsonb Object 是否包含指定的 key (單一條件, String)。直接使用 ?& 替代即可。
    • <@ : 右側 Jsonb (條件)是否包含左側 Jsonb (資料)內容。與 @> 差異只是條件左右對調。

Jsonb Where extensions

operator條件參數 (右式)說明使用情境
@>Json Object
key-value pair
'{"k1": "v1", "k2": "v2"}'		右式資料是否存在於左式Jsonb 中的 where 子句
?|Text Array
TEXT[]
'["k1","k2"]'		右式鍵值是否 任一 存在於左式頂層鍵web form filter 缺值不考慮
?&Text Array
TEXT[]
'["k1","k2"]'		右式鍵值是否 全部 存在於左式頂層鍵DAO 考量傳入參數空值時必須為空

  • 範例情境: 四支手機的商品規格

    CREATE TABLE products (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL,
        attributes JSONB
    );
    
    -- 新增一些範例資料
    INSERT INTO products (name, attributes) VALUES
        ('iPhone 15 Pro', '{"brand": "Apple", "color": "天然鈦金屬", "storage": "256GB", "features": ["A17 Pro Chip", "USB-C"], "dimensions": {"height": 146.6, "width": 70.6}}'),
        ('Samsung Galaxy S24', '{"brand": "Samsung", "color": "紫色", "storage": "512GB", "features": ["Galaxy AI", "Snapdragon 8 Gen 3"], "has_sd_card_slot": false}'),
        ('Google Pixel 8', '{"brand": "Google", "color": "曜石黑", "storage": "128GB", "features": ["Google Tensor G3", "Magic Eraser"]}'),
        ('二手 iPhone 13', '{"brand": "Apple", "color": "藍色", "storage": "256GB", "condition": "used"}'),
        ('Sony Xperia 1 VII', '{"brand": "Sony",  "color": null}'),
        ('Asus Zenfone 11 ultra', '{"brand": "Asus",  "storage": "512GB"}');

Jsonb k/v Pair 為條件進行 Row 查詢:  @> ​

  •  @> : Contains KV pair，(emoticon : look at right side)
  • 概念就是用在 Jsonb 中的 where 子句。
  • 若條件(右式)出現重複的 keys，意即 field 重複給予。則後令修前令，只會以較晚指定為準。
    -- 查詢 Apple 256G 的手機 
    select name, attributes
        from products
        where attributes @> '{"brand": "Apple", "storage": "256GB"}';
    
    --> iPhone 15 Pro, 二手 iPhone 13

Jsonb 是否同時存在頂層鍵:  ?& ​

  •  ?& : Contains All keys，(emoticon : exists + and)
  • 條件以 String Array 傳遞
  • 概念就是用在 Jsonb 中的 exists、not exists 判斷。
  • 使用情境: Service/DAO 參數，null 不忽略。。
    select name, attributes
        from products   
        where attributes ?& ARRAY['brand', 'color', 'storage'];
        
    -- 概念同上，在考量 Jsonb GIN index 最佳化調整，建議用 ?&
    select name, attributes
        from products
        where attributes ? 'brand' and attributes ? 'color' and attributes ? 'storage' ;

Jsonb 是否存在任一頂層鍵:  ?| ​

  •  ?| : Contains Any keys，(emoticon : exists + or)
  • 條件也是以 String Array 傳遞
  • 使用情境: 串接 Web Form 的查詢條件，null 視為忽略。
    select name, attributes
        from products   
        where attributes ?| ARRAY['dimensions', 'condition'];
        
    -- 概念同上，在考量 Jsonb GIN index 最佳化調整，建議用 ?|
    select name, attributes
        from products
        where attributes ? 'dimensions' or attributes ? 'condition' ;

Jsonb 是否存在頂層鍵:  ? ​

  •  ? : 指定 top-level key 是否存在。不考慮 value 狀態。
  • 概念就是用在 Jsonb 中的 exists、not exists 判斷。
  • 可用 ?& 取代: 判斷同時存在多個 top-level keys。
    -- 逐筆(row) 檢查，將有指定 key 的 row 選出。
    -- Sony 有 color key，但未賦值會被選出。
    select name, attributes
        from products
        where attributes ? 'color'; 
    
    -- 等同於    
    select name, attributes
        from products
        where attributes ->> 'color' is not  null ; 
    
    -- 反向選擇需加 Not  
    select name, attributes
        from products
        where NOT (attributes ? 'color');
    -- 註: 檢查的是 key 
    -- 所以: Sony Xperia:  "color": null  有 key 所以不會被選出