pgsql中的json操作

基于存储的 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 数据的操作。