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;
其他函数读者可以参考官方文档。