PostgreSQL JSON 数据类型

本文介绍了在 PostgreSQL 中如何使用 JSON 类型以及一些用于处理 JSON 数据的有用的函数。

JSON 是一种通用的、轻量级的数据传输格式,它可以表示复杂的、结构化的数据,但同时也易于阅读和编写。

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

JSON 简介

JSON 定义了以下几种类型的值:

  • 对象
  • 数组
  • 字符串
  • 数字
  • true
  • false
  • null

JSON 对象是一个键值对的组合。键是字符串类型的文本,值可以是上面定义的几种类型的任何一种。

JSON 数组是一些值的组合。其中的值可以是任意的以上一种类型的值。

一个 JSON 值可以是以上任意类型的值。

比如,以下是一些纯值的 JSON 值:

"a"
"abc"
"Hello World"
1
1.23
123.456
true
false
null

以下是一个 JSON 对象的示例:

{
  "x": "Hollo World",
  "y": { "a": 1, "b": true },
  "z": [1, "a", true, false, null],
  "o": null
}

以下是一个 JSON 数组的示例:

[1, "a", { "a": 1, "b": true }, [1, "a", true, false, null], null]

通常情况下,我们在传输数据的时使用 JSON 对象和 JSON 数组。

PostgreSQL JSON 介绍

PostgreSQL 支持两种 JSON 类型: JSONJSONB。他们的处理效率不同。 JSON 按文本保存输入数据,保留原始数据中的空格,重复键。 JSONB 则按照二进制保存输入数据,他会删除没必要的空格以及重复的键。

JSON 文本转为 JSON 类型

要将一个字符串的 JSON 文本转为 JSON 类型的值,您可以使用如下两种方法:

  • 使用 JSON() 构造函数将 JSON 文本转为 JSON 类型的值:
json('[1, 2, 3]')
  • 使用类型转换将 JSON 文本转为 JSON 类型的值:
'{"a": [1, 2, 3]}'::json

JSON 操作符

PostgreSQL 提供了几个用于 JSON 数据的操作符,如下:

  • ->: 获取 JSON 数组的元素或者 JSON 对象中的字段,返回值为 JSON 类型的值。
  • ->>: 获取 JSON 数组的元素或者 JSON 对象中的字段,返回值为文本。
  • #>: 获取指定路径的值,返回值为 JSON 类型的值。
  • #>>: 获取指定路径的值,返回值为文本。

PostgreSQL JSON 实例

使用下面的语句创建一个新表,名称为 login_logs:

CREATE TABLE login_logs (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  detail JSON NOT NULL
);

login_logs 表由两列组成:

  1. id 列是标识每行的主键列,它是一个标识列
  2. detail 是 JSON 数据类型。

插入 JSON 数据

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

INSERT INTO login_logs (detail)
VALUES
  ('{ "name": "Tom", "address": {"ip": "192.168.1.23","country": "US"}}'),
  ('{ "name": "Tim", "address": {"ip": "192.168.1.24","country": "US"}}'),
  ('{ "name": "Jim", "address": {"ip": "192.168.1.25","country": "UK"}}');

这里, detail 列中的 JSON 值是 JSON 对象,其中包含两个键:

  • name 是登陆用户的名称。
  • address 是登陆用户的地址,它也是一个 JSON 对象,包含了两个成员:
    • ip 是登陆用户的 IP 地址。
    • country 是登陆用户所在的国家。

查询 JSON 数据

要查询 JSON 数据,请使用该 SELECT 语句:

SELECT * FROM login_logs;
 id |                               detail
----+---------------------------------------------------------------------
  1 | { "name": "Tom", "address": {"ip": "192.168.1.23","country": "US"}}
  2 | { "name": "Tim", "address": {"ip": "192.168.1.24","country": "US"}}
  3 | { "name": "Jim", "address": {"ip": "192.168.1.25","country": "UK"}}
(3 rows)

如果您想显示 JSON 对象内部的字段,请使用 ->->> 操作符。 -> 返回的值是 JSON 类型, ->> 返回的值是文本类型。

以下查询分别使用两个运算符 ->->> 以 JSON 的形式获取所有登陆用户的名称:

SELECT
  detail -> 'name' AS name_json,
  detail ->> 'name' AS name_text
FROM
  login_logs;
 name_json | name_text
-----------+-----------
 "Tom"     | Tom
 "Tim"     | Tim
 "Jim"     | Jim
(3 rows)

要获取所有登陆用的国家和 IP,请使用下面的语句:

SELECT
  detail -> 'address' ->> 'country' as country,
  detail -> 'address' ->> 'ip' as ip
FROM login_logs;
 country |      ip
---------+--------------
 US      | 192.168.1.23
 US      | 192.168.1.24
 UK      | 192.168.1.25
(3 rows)

首先 detail -> 'address' 将地址作为 JSON 对象返回。然后 detail -> 'address' ->> 'country' 将所有国家作为文本返回。

在 WHERE 条件中使用 JSON 运算符

您可以在 WHERE 子句中使用 JSON 运算符来过滤返回的行。

例如,要找出所有来自 US 的登陆用户,清使用以下查询:

SELECT
  detail ->> 'name' AS name,
  detail -> 'address' ->> 'country' AS country
FROM
  login_logs
WHERE
  detail -> 'address' ->> 'country' = 'US';
 name | country
------+---------
 Tom  | US
 Tim  | US
(2 rows)

PostgreSQL JSON 函数

PostgreSQL 提供了很多 JSON 相关的函数,如下:

  • array_to_json(): 将一个 SQL 数组转为 JSON 数组并返回。
  • json_array_elements(): 将顶层 JSON 数组扩展为一个 JSON 值的集合。
  • json_array_length(): 返回一个指定的 JSON 数组的长度。
  • json_build_array(): 从可变的参数列表中创建一个可能是异构类型的 JSON 数组。
  • json_each(): 将一个指定的 JSON 对象扩展为一个键值对的集合。
  • json_extract_path(): 从一个指定的 JSON 值中提取指定的路径的值。
  • json_object_keys(): 返回指定的 JSON 对象中的顶层键的集合。
  • json_object(): 从一个文本数组构建一个 JSON 对象,或者从两个分别作为键和值的数组构建一个 JSON 对象。
  • json_to_record(): 将指定的最顶层的 JSON 对象扩展为一个在 AS 子句中定义的具有符合类型的行。
  • json_to_recordset(): 将指定的最顶层的 JSON 数组(元素为对象)扩展为一个在 AS 子句中定义的具有符合类型的行的集合。
  • json_typeof(): 以字符串的形式返回指定的 JSON 值的类型。
  • row_to_json(): 将一个 SQL 复合类型的值转为 JSON 对象并返回。
  • to_json(): 将一个 SQL 值转为 JSON 值并返回。

结论

本文讨论了 PostgreSQL JSON 数据类型,以及一些常用的 JSON 操作符和函数。