PostgreSQL JSON数据类型
本文我们学习PostgreSQL JSON数据类型,同时也介绍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数据
插入json数据
要插入json数据,需确保数据是格式规范的json。下面insert语句插入一行order数据:
INSERT INTO orders (info)
VALUES
(
'{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'
);
表示,john Doe买了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}}'
);
查询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’返回所有产品文本值。
在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进行比较。
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 |
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 |
其他函数读者可以参考官方文档。
总结
本文我们学习PostgreSQL JSON数据类型。主要通过实例展示了最常用的json操作符和函数,让我们更高效地处理json数据。