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