PostgreSQL 教程: JSON 数据类型

摘要：在本教程中，您将学习如何使用 PostgreSQL JSON 数据类型，以及一些用于处理 JSON 数据的有用运算符和函数。

JSON 代表 JavaScript 对象表示法（JavaScript Object Notation）。JSON 是一种由键值对组成的开放标准格式。

JSON 的主要用途是在服务器和 Web 应用程序之间传输数据。与其他格式不同，JSON 是人类可读的文本。

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

让我们开始创建一个新表来练习 JSON 数据类型。

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

该orders表由两列组成：

1. id列是标识订单的主键列。
2. info列以 JSON 形式存储数据。

插入 JSON 数据

要将数据插入 JSON 列，您必须确保数据采用有效的 JSON 格式。以下INSERT语句将新行插入orders表中。

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

意思是John Doe买了6瓶beers。

以下语句同时插入多行。

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

要查询 JSON 数据，您可以使用以下SELECT语句，这与查询其他原生数据类型类似：

SELECT info FROM orders;

PostgreSQL 以 JSON 形式返回结果集。

PostgreSQL 提供了两个原生运算符->和->>来帮助您查询 JSON 数据。

• 运算符->按键返回 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'将商品项作为 JSON 对象返回。然后使用info->'items'->>'product'以文本形式返回所有产品。

在 WHERE 子句中使用 JSON 运算符

我们可以在WHERE子句中使用 JSON 运算符来过滤返回的行。例如，要找出谁购买了Diaper，我们使用以下查询：

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

要找出谁同时购买了两个产品，我们使用以下查询：

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

请注意，我们使用类型转换将qty字段转换为INTEGER类型并将其与 2 进行比较。

将聚合函数应用于 JSON 数据

我们可以将 MIN、MAX、AVG、SUM 等聚合函数应用于 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 函数

PostgreSQL 为我们提供了一些函数来帮助您处理 JSON 数据。

json_each 函数

json_each()函数允许我们将最外层的 JSON 对象扩展为一组键值对。请查看下面的语句：

SELECT json_each (info)
FROM orders;

如果您想获取一组键值对作为文本，则可以使用json_each_text()函数。

json_object_keys 函数

要获取最外层 JSON 对象中的一组键，请使用json_object_keys()函数。以下查询返回info列中嵌套的items对象的所有键。

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

json_typeof 函数

json_typeof()函数以字符串形式返回最外层 JSON 值的类型。它可以是number、boolean、null、object、array和string。

以下查询返回商品项的数据类型：

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

以下查询返回嵌套的商品项 JSON 对象的 qty 字段的数据类型。

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

如果您想深入挖掘，还有更多 PostgreSQL JSON 函数。

在本教程中，您学习了如何使用 PostgreSQL JSON 数据类型，以及如何使用一些最重要的 JSON 运算符来更有效地处理 JSON 数据。