Postgres - JSON 类型实例示例

Postgres - JSON 类型 实例示例

json会输出和输入完全相同的文本，而jsonb 则不会保留语义上没有意义的细节（例如空格）。

• 1. 概述
• 2. 创建数据表 create table( 字段类型：json )
• 3. 插入json数据
• 4. 查询json数据
• 5. 按键值 返回JSON 对象字段
• 6. 按文本返回JSON对象字段
• 7. 在where子句中使用json操作符
• 8. JSON 数据的聚集函数：min,max,average,sum
• 9. PostgreSQL JSON 函数 - json_each()
• 10. PostgreSQL JSON 函数 - json_object_keys()
• 11. PostgreSQL JSON 函数 - json_typeof()

1. 概述

JSON 代表 JavaScript Object Notation。JSON是开放的标准格式，由key-value对组成。JSON的主要用于在服务器与web应用之间传输数据。与其他格式不同，JSON是人类可读的文本格式。
PostgreSQL从9.2版本开始支持JSON数据类型，并提供很多函数和操作符维护json数据。

2. 创建数据表

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

orders表包括两列：

• id是主键，为自增列
• info列存储json数据

3. 插入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}}'
   );

4. 查询json数据

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

SELECT info FROM  orders;

5. 按键值 返回JSON 对象字段

PostgreSQL 默认提供了两个操作符用于查询json数据：

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

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

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

6. 按文本返回JSON对象字段

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

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

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

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

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

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

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

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

返回结果：

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

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

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

8. json 数据的聚集函数min,max,average,sum

我们能对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

9. PostgreSQL JSON 函数 - json_each()

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

SELECT
   json_each (info)
FROM
   orders;

如果想得到一组key-value对作为文本，可以使用json_each_text()函数。

10. PostgreSQL JSON 函数 - json_object_keys()

json_object_keys()函数可以获得json对象最外层的一组键。举例：

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

11. PostgreSQL JSON 函数 - json_typeof()

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

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

查询结果：

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

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

其他函数读者可以参考官方文档。

参考：https://blog.csdn.net/neweastsun/article/details/93345799