PostgreSQL JSON查询

JSON 代表 JavaScript Object Notation。JSON是开放的标准格式，由key-value对组成。JSON的主要用于在服务器与web应用之间传输数据。与其他格式不同，JSON是人类可读的文本格式。

PostgreSQL从9.2版本开始支持JSON数据类型，并提供很多函数和操作符维护json数据。下面我们创建一张新表用于说明json数据类型：

CREATE TABLE orders (
   ID serial NOT NULL PRIMARY KEY,
   info json NOT NULL
);

orders表包括两列：
id是主键，为自增列
info列存储json数据

1.插入json数据

要插入json数据，需确保数据是格式规范的json。下面insert语句插入一行order数据：

INSERT INTO orders (info)
VALUES
   (
      '{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'
   );

下面同时插入多行。

INSERT INTO orders (info)
VALUES
   (
      '{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'
   ),
   (
      '{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'
   ),
   (
      '{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'
   );

2.查询json数据

使用select语句查询json数据，与其他基本数据类型类似：

SELECT
   info
FROM
   orders;

PostgreSQL返回一组JSON数据。PostgreSQL 默认提供了两个操作符用于查询json数据：

The operator -> 按键返回 JSON 对象字段.
The operator ->> 按文本返回JSON对象字段.

下面查询使用->操作符，查询json中所有顾客作为键:

SELECT
   info -> 'customer' AS customer
FROM
   orders;

下面使用->>操作获取所有顾客作为文本：

SELECT
   info ->> 'customer' AS customer
FROM
   orders;

->操作返回json对象，我们可以链式方式继续使用->>返回特定节点。下面语句返回所有购买的商品：

SELECT
   info -> 'items' ->> 'product' as product
FROM
   orders
ORDER BY
   product;

首先使用info->'item’返回json对象。然后使用info->‘item’->>'product’返回所有产品文本值。

3.在where子句中使用json操作符

我们能在where子句中使用json操作符过滤数据行。举例，查找买了Diaper的记录：

SELECT
   info ->> 'customer' AS customer
FROM
   orders
WHERE
   info -> 'items' ->> 'product' = 'Diaper'

返回结果：

customer
Lily Bush

下面查询谁一次买了2个商品，语句如下：

SELECT
   info ->> 'customer' AS customer,
   info -> 'items' ->> 'product' AS product
FROM
   orders
WHERE
   CAST (
      info -> 'items' ->> 'qty' AS INTEGER
   ) = 2

结果如下：

customer	product
Mary Clark	Toy Train

注意我们使用cast转换qty字段值为integer类型，然后和2进行比较。

4.json 数据的聚集函数

我们能对json数据使用聚集函数，如min,max,average,sum等。举例，下面语句返回最小数量，最大数量、平均数量以及总数量。

SELECT
   MIN (
      CAST (
         info -> 'items' ->> 'qty' AS INTEGER
      )
   ),
   MAX (
      CAST (
         info -> 'items' ->> 'qty' AS INTEGER
      )
   ),
   SUM (
      CAST (
         info -> 'items' ->> 'qty' AS INTEGER
      )
   ),
   AVG (
      CAST (
         info -> 'items' ->> 'qty' AS INTEGER
      )
   )
FROM
   orders

结果

min	max	sum	avg
1	2	24	8.25

5.PostgreSQL JSON 函数

json_each()函数的作用是:将最外层的JSON对象展开为一组键值对。举例：

SELECT
   json_each (info)
FROM
   orders;

如果想得到一组key-value对作为文本，可以使用json_each_text()函数。
json_object_keys()函数可以获得json对象最外层的一组键。举例：

SELECT
   json_object_keys (info->'items')
FROM
   orders;

json_typeof函数返回json最外层key的数据类型作为字符串。可能是number, boolean, null, object, array, string。下面语句查询所有item的数据类型：

SELECT
   json_typeof (info->'items')
FROM
   orders;

返回结果为：

json_typeof
object
object
object
object

下面语句返回嵌套类型中qty字段的数据类型：

SELECT
   json_typeof (info->'items'->'qty')
FROM
   orders;

返回结果：

json_typeof
number
number
number
number