提要:在本教程中,我们将向您展示如何使用PostgreSQL JSON数据类型。 另外,我们将向您介绍一些用于处理JSON数据的最常见的PostgreSQL JSON运算符和函数。

JSON 代表 JavaScript Object Notation,它是由键值对组成的开放标准格式。JSON 的主要用途是在服务器和 Web 应用程序之间传输数据。与其他格式不同,JSON 是人类可读的文本。

PostgreSQL 从 9.2 版开始提供对 JSON 的原生支持。它提供了许多操作 JSON 数据的函数和运算符。

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

插入 JSON 数据

要将数据插入到 JSON 字段中,必须确保数据是有效的 JSON 格式。

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

它表示,客户(customersjk66 买了 6Beer(啤酒)。

插入更多数据:

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 数据:

  • -> 运算符通过键来返回 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 -> 'items' 将所有的 items 返回为 JSON 对象,然后 ->>'product' 将所有产品以文本进行返回。

在 WHERE 中使用 JSON 运算符

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

在 JSON 数据中应用聚合函数

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

PostgreSQL 的 JSON 函数

JSON_EACH() 函数

json_each() 函数允许我们将最外层的 JSON 对象扩展为一组键值对。

SELECT
 json_each (info)
FROM
 orders;

如果要把键值对变成文本,可以使用 json_each_text() 函数来代替 json_each() 函数。

json_object_keys() 函数

使用 json_object_keys() 函数,获取最外层 JSON 的所有键。

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

json_typeof() 函数

json_typeof() 函数用于判断数据类型(JSON 里的数据类型),它的值可能是:number, boolean, null, object, arraystring

SELECT
 json_typeof (info->'items')
FROM
 orders;
下一篇:自定义数据类型