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 TABLE
postgres=# INSERT INTO aa VALUES (1, '{"f1":1,"f2":true,"f3":"Hi I''m \"Daisy\""}');
INSERT 0 1
postgres=# INSERT INTO aa VALUES (2, '{"f1":{"f11":11,"f12":12},"f2":2}');
INSERT 0 1
postgres=# 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