PostgreSQL jsonb_path_query()查詢JSON範例

PostgreSQL的jsonb_path_query(target jsonb, path jsonpath)函式透過輸入jsonpath篩選jsonb的資料。

jsonpath類似JavaScript存取物件/陣列中特定元素的表示式。

例如employee資料表目前有以下資料，欄位contact的型態為jsonb。

 id | name | age |                         contact                         |         created_at
----+------+-----+---------------------------------------------------------+----------------------------
  1 | john |  33 | {"name": "mary", "phone": ["0912345678", "0912345679"]} | 2022-02-15 21:33:43.578068
  2 | tony |  23 | {"name": "bill", "phone": ["0921345678", "0921345679"]} | 2022-02-19 14:53:17.520679

例如下面查詢contact的key為phone的陣列索引為0的值。

SELECT
  jsonb_path_query(contact, '$.phone[0]') as phone_1
FROM employee;

postgres=> SELECT jsonb_path_query(contact, '$.phone[0]') as phone_1 FROM employee;
   phone_1
--------------
 "0912345678"
 "0921345678"

在jsonpath後可加上?依條件篩選值，條件式放在括弧內，而要篩選的對象以@表示。

例如下面查詢contact的phone陣列中為0912345678的資料。

SELECT
  name, 
  jsonb_path_query(contact, '$.phone[*] ? (@ == "0912345678")') as phone 
FROM employee;

postgres=> SELECT name, jsonb_path_query(contact, '$.phone[*] ? (@ == "0912345678")') as phone FROM employee;
 name |    phone
------+--------------
 john | "0912345678"

• JSON Functions and Operators