PostgreSQL 教程: JSON 数据类型

八月 23, 2023

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

目录

PostgreSQL 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买了6beers

以下语句同时插入多行。

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 形式返回结果集。

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

  • 运算符->按键返回 JSON 对象字段。
  • 运算符->>以文本形式返回 JSON 对象字段。

以下查询使用运算符->以 JSON 形式获取所有客户:

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

postgresql JSON 原生运算符

以下查询使用运算符->>以文本形式获取所有客户:

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

postgresql json 文本运算符

由于->运算符返回 JSON 对象,因此您可以将其与运算符->>串接起来以检索特定节点。例如,以下语句返回所有已售出的产品:

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

postgresql json 运算符串接

首先info -> 'items'将商品项作为 JSON 对象返回。然后使用info->'items'->>'product'以文本形式返回所有产品。

在 WHERE 子句中使用 JSON 运算符

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

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

postgresql json opeartor in where clause

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

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

postgresql json operator cast in where clause

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

将聚合函数应用于 JSON 数据

我们可以将 MINMAXAVGSUM聚合函数应用于 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 函数

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

json_each 函数

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

SELECT json_each (info)
FROM orders;

postgresql json json_each 函数

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

json_object_keys 函数

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

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

postgresql json_object_keys 函数

json_typeof 函数

json_typeof()函数以字符串形式返回最外层 JSON 值的类型。它可以是numberbooleannullobjectarraystring

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

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

postgresql json_typeof

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

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

postgresql json_typeof 函数

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

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

了解更多

PostgreSQL 教程:数据类型

PostgreSQL 文档:JSON 类型