PostgreSQL JSON ->操作符

PostgreSQL的->操作符可用來篩選JSON型態(json、jsonb)的資料，取得的結果為JSON物件。

例如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名稱）或整數（陣列索引）來篩選JSON資料的內容。

例如下面查詢JSON的key為name的值。

postgres=> SELECT contact -> 'name' as contact_name FROM employee;
 contact_name
--------------
 "mary"
 "bill"

下面查詢JSON的key為phone，然後取得陣列索引為0的值。

postgres=> SELECT contact -> 'phone' -> 0 as phone_1 FROM employee;
   phone_1
--------------
 "0912345678"
 "0921345678"

查詢時WHERE條件也可使用->依JSON資料做篩選。

例如下面查詢contact JSON資料中key name值為bill的資料。由於contact型態為jsonb，所以使用to_jsonb()將參數字串轉為jsonb來比較。

SELECT * FROM employee 
WHERE contact -> 'name' = to_jsonb('bill'::text);

查詢結果。

postgres=> SELECT * FROM employee WHERE contact -> 'name' = to_jsonb('bill'::text);
 id | name | age |                         contact                         |         created_at
----+------+-----+---------------------------------------------------------+----------------------------
  2 | tony |  23 | {"name": "bill", "phone": ["0921345678", "0921345679"]} | 2022-02-19 14:53:17.520679

• JSON Functions and Operators
• PostgreSQL JSON