PostgreSQL 9.3 新特性預覽 —— JSON 操作

Postgres 9.3 目前正在緊鑼密鼓的開發中，該版本值得關注的一個新特性就是 JSON 資料類型。在看完 new functions for data generation 這篇文章後，我們來看看在 commit 記錄中關於新的 JSON 特性的說明：

commit a570c98d7fa0841f17bbf51d62d02d9e493c7fcc
Author: Andrew Dunstan
Date: Fri Mar 29 14:12:13 2013 -0400
　
Add new JSON processing functions and parser API.
　
The JSON parser is converted into a recursive descent parser, and
exposed for use by other modules such as extensions. The API provides
hooks for all the significant parser event such as the beginning and end
of objects and arrays, and providing functions to handle these hooks
allows for fairly simple construction of a wide variety of JSON
processing functions. A set of new basic processing functions and
operators is also added, which use this API, including operations to
extract array elements, object fields, get the length of arrays and the
set of keys of a field, deconstruct an object into a set of key/value
pairs, and create records from JSON objects and arrays of objects.
　
Catalog version bumped.
　
Andrew Dunstan, with some documentation assistance from Merlin Moncure.

基於儲存的 JSON 資料，該提交還引入新的 API、運算子和函數用來操作 JSON 資料，共有 4 個運算子和8個新的函數，本文只簡單介紹 4 個新的運算子。

下列的資料集用於文章中所有實驗：

postgres=# CREATE TABLE aa (a int, b json);CREATE TABLEpostgres=# INSERT INTO aa VALUES (1, '{"f1":1,"f2":true,"f3":"Hi I''m \"Daisy\""}');INSERT 0 1postgres=# INSERT INTO aa VALUES (2, '{"f1":{"f11":11,"f12":12},"f2":2}');INSERT 0 1postgres=# INSERT INTO aa VALUES (3, '{"f1":[1,"Robert \"M\"",true],"f2":[2,"Kevin \"K\"",false]}');INSERT 0 1

 

第一個運算子是 “->”, 用來直接從 JSON 資料庫擷取欄位值，使用文本值來標註欄位的鍵：

postgres=# SELECT b->'f1' AS f1, b->'f3' AS f3 FROM aa WHERE a = 1;f1 | f3----+--------------------1 | "Hi I'm \"Daisy\""(1 row)

也可以使用多個鍵來擷取資料或者另外一個子集資料：

postgres=# SELECT b->'f1'->'f12' AS f12 FROM aa WHERE a = 2;f12-----12(1 row)postgres=# SELECT b->'f1' AS f1 FROM aa WHERE a = 2;f1---------------------{"f11":11,"f12":12}(1 row)

另外一個更有趣的方法，當使用整數作為鍵時，你可直接從儲存的數組擷取資料：

postgres=# SELECT b->'f1'->0 as f1_0 FROM aa WHERE a = 3;f1_0------1(1 row)

第二個運算子是 “->>”. 與 “->” 不同的是，該運算子返回指定的文本，“->>” 返回純文字。

postgres=# SELECT b->>'f3' AS f1 FROM aa WHERE a = 1;f1----------------Hi I'm "Daisy"(1 row)postgres=# SELECT b->'f3' AS f1 FROM aa WHERE a = 1;f1--------------------"Hi I'm \"Daisy\""(1 row)

與 “->” 相同的是，->> 也可以使用整數或者文本作為鍵，使用整數時代表在數組中的位置：

postgres=# SELECT b->'f1'->>1 as f1_0 FROM aa WHERE a = 3;f1_0------------Robert "M"(1 row)postgres=# SELECT b->'f1'->1 as f1_0 FROM aa WHERE a = 3;f1_0----------------"Robert \"M\""(1 row)

當然，你不能通過欄位名來擷取資料中的資料：

postgres=# SELECT b->'f1'->>'1' as f1_0 FROM aa WHERE a = 3;ERROR: cannot extract field from a non-object

同樣你不能使用元素數值來擷取欄位一樣：

postgres=# SELECT b->1 as f1_0 FROM aa WHERE a = 3;ERROR: cannot extract array element from a non-array

最後兩個運算子是 “#>” 和 “#>>”. 用來直接擷取數組中的元素而無需使用前面兩種方法“column->’$FIELD’->$INT_INDEX. 這可以讓你的查詢更加具備可讀性。

postgres=# SELECT b#>'{f1,1}' as f1_0 FROM aa WHERE a = 3;f1_0----------------"Robert \"M\""(1 row)postgres=# SELECT b#>>'{f1,1}' as f1_0 FROM aa WHERE a = 3;f1_0------------Robert "M"(1 row)

“#>” 使用有效 JSON 格式擷取文本資料，而 “#>>” 則返回純文字。

 

總結一句，這些新的操作符大大方便了很多應用對 JSON 資料的操作。

via otacoo