Postgres 的 JSON 操作，早已成为 RFC 官方标准！

介绍

在过去的 15 年里，JSON 已成为默认的系统和系统之间的数据传输协议。它简单、灵活且可扩展。API 已采用 JSON。用户身份验证系统使用 JSON。在创建多年后，它进入了官方标准：RFC 7159。

PostgreSQL 有两种 JSON 数据类型：JSONB 和 JSON。JSONB 是 JSON 优化后的二进制版本，存储速度较慢，但针对查询和处理进行了优化。JSON 是数据的原始副本，查询能力有限。在本教程中，我们将专门使用 JSONB。

JSONB 数据类型

在 PostgreSQL 中，JSONB 只是一种数据类型。与许多数据类型一样，它带有自己的函数集。在 PostgreSQL 中，这些函数提供了提取、比较、创建和操作 JSONB 的能力。要涵盖 JSONB 的全部可能的用法（和注意事项），需要很长的时间，本教程旨在推动您沿着这条路径进行自己的探索。

首先，一个简单的 JSONB 对象可以像下面一样简单：

SELECT '{"name": "batman", "superpower": "fighting skills"}'::jsonb;

数组可以是 JSONB 的顶层元素：

SELECT '["batman", "superman"]'::jsonb;

JSONB 可以是嵌套在数组中的对象。使用数组和对象，你可以让结构达到你想要的任意深度！

SELECT '[{"name": "batman"}, {"name": "superman"}]'::jsonb;

使用jsonb_to_recordset，我们可以将数组转换为一组记录：

SELECT
    *
FROM jsonb_to_recordset('[{"name": "batman"}, {"name": "superman"}]'::jsonb) AS x(name TEXT);

正如我们刚刚展示的从 JSON 转换为记录集一样，我们还可以将记录集转换回 JSON。您可以将任何对象转换为 JSONB，包括表中的一行：

SELECT
    to_jsonb(employees)
FROM employees
LIMIT 5;

如果要筛选列，可执行下面的操作：

SELECT
    to_jsonb(truncated_employees)
FROM (
    SELECT first_name, last_name FROM employees LIMIT 10
) AS truncated_employees;

从 JSONB 数据中提取数据

有两种不同的方法可以从 JSONB 数据中提取数据：运算符和 JSON 路径。运算符如下所示。

SELECT
    ('{"name": "batman", "superpower": "fighting skills"}'::jsonb)->'name';

上面，我们使用->运算符从 json 对象中提取名称。请注意，此示例和下面示例使用的运算符略有不同，但相似。我们稍后会解释其中的区别。接下来，我们提取一个数组的第一个值：

SELECT
    ('[{"name": "batman"}, {"name": "superman"}]'::jsonb)[0]->>'name';

我们使用了数组表示法[0]，返回数组的第一个元素，然后使用了->>运算符返回 name 属性的值。

对于顶层数组，请使用0作为要检索的值：

SELECT
    ('["batman", "superman"]'::jsonb)->>0;

如果您查看 PostgreSQL JSON 函数，您会看到大量的 JSON 操作和查询运算符。

上面是使用运算符的示例，下面我们将使用 JSON 路径。JSON 路径允许进行更具表现力的操作和提取。下面，我们使用 JSON 路径而不是运算符，来改写前面的 2 个示例：

而且，如果您想对数组运行查询，它将如下所示：

SELECT
    jsonb_path_query(('[{"name": "batman"}, {"name": "superman"}]'::jsonb), '$[0].name');

但是还有更多。使用 JSON 路径，您还可以使用下面的$.key语法，提取数组中对象的值：

SELECT
    jsonb_path_query(('[{"name": "batman"}, {"name": "superman"}]'::jsonb), '$.name');

JSON 路径比这要深入得多。您可以执行 Javascript 也允许的许多操作，例如数学运算、正则表达式、字符串操作等。

创建 JSONB 列

创建 JSONB 列与创建任何列类似。下面，我们在 employees 表中添加了一个my_new_json_column列：

ALTER TABLE employees ADD COLUMN my_new_json_column JSONB DEFAULT NULL;

此语句使用ALTER TABLE命令添加数据类型为 JSONB 的列。

将数据插入 JSONB 列

将新记录插入 JSONB 非常简单：

INSERT INTO employees (first_name, last_name, start_date, job_title, systems_access) VALUES
('Chris', 'Winslett', current_date, 'Master of the Highwire', '{"crm": 1662186380, "helpdesk": 1663290134, "admin_panel": null}');

在上面，我们使用了一个对象，其键表示系统的名称，并使用纪元时间戳来表示该人上次访问该特定系统的时间。

顶层对象：如何查询

对于顶层对象查询，我们将使用systems_access列。它已填充了一个 1 层对象，该对象中有键值对，其中的键表示系统，值表示该人员上次与系统交互的纪元时间戳，例如：

{
    "crm": 1663290134.655640,
    "helpdesk": 1662186380.689519,
    "admin_panel": null
}

我们之所以选择 EPOCH 时间戳，是因为它可以避免日期转换的一些复杂性。通过使用浮点数，在后面的示例中，我们可以简单地索引 JSONB 中包含的浮点数，而日期值会超出教程所要求的复杂度。具体的复杂性是因为将文本强制转换为日期不是不可变的，因此不能用于构建索引。

让我们将此 JSONB 对象作为 SELECT 语句的一部分返回：

SELECT
    systems_access
FROM employees
WHERE id = 1;

我们甚至可以检索systems_access中的值。下面，我们使用两个不同的运算符，返回crm和helpdesk键中的值：

SELECT
    systems_access->'crm' AS crm_value,
    systems_access->>'helpdesk' AS helpdesk_value
FROM employees
WHERE id = 1;

返回的会是哪些数据类型？下面，我们用pg_typeof来检查返回的数据类型：

SELECT
    pg_typeof(systems_access->'crm') AS crm_type,
    pg_typeof(systems_access->>'helpdesk') AS helpdesk_type
FROM employees
WHERE id = 1;

一个是jsonb，另一个是text，但为什么呢？如果您仔细查看查询，在返回crm值时，我们使用的是->，在返回helpdesk值时，我们使用的是->>。由于返回的数据类型不同，这种语法差异很重要。

可以想象，对于条件语句中所需的比较，了解返回的对象类型非常重要。与 Javascript 不同，PostgreSQL 需要相当明确的数据类型，才能进行正确的比较（日期除外，但这需要另一篇教程来讲了）。

让我们运行一次查询，找出过去 7 天内访问过crm的每个人。换句话说，我们如何在systems_access这个 JSONB 字段中为crm键应用条件？下面是要筛选这些值的查询。我们是否可以运行如下所示的操作？

SELECT
    first_name,
    last_name
FROM employees
WHERE systems_access->>'crm' > EXTRACT('epoch' FROM now() - '7 day'::interval);

但是，答案是 '不行'，因为我们收到了错误消息：

ERROR:  operator does not exist: text > numeric
LINE 5: WHERE systems_access->>'crm' > EXTRACT('epoch' FROM now() - ...

PostgreSQL 不会将文本与数字进行比较。为什么systems_access->>'crm'是文本而不是数字？存储 JSONB 时，每个值都是文本。我们有两个选择：以文本来比较相等性，或者将类型转换为数值。以文本进行比较会呈现出不正确的结果。

SELECT
    first_name,
    last_name
FROM employees
WHERE
    (systems_access->>'crm')::float > EXTRACT('epoch' FROM now() - '7 day'::interval);

现在，命令已正确运行！它会返回您想要的记录。按下q键退出返回的长列表。要查看从未访问过的用户，让我们查找 CRM 值为 NULL 的用户：

SELECT
    first_name,
    last_name
FROM employees
WHERE
    (systems_access->>'crm') IS NULL;

按下q键退出返回的长列表。尝试对此进行转换（例如::float） — 尝试将其移除。对于这种比较来说，转换还重要吗？当您在 PostgreSQL 中探索 JSONB 查询时，请记住->和->>之间存在差异，以及如何强制转换值，以进行适当的比较。

顶层对象：如何更新

有两种方法可以更新 JSONB 列：1）更新整个数据，或者 2）更新部分数据。旁注：对于这两种方法，PostgreSQL 都将更改视为一次完整更新，我们只使用函数来计算新值，并避免来自应用程序层的往返。

更新整个列很简单：

UPDATE employees
SET
    systems_access = '{"crm": 1661637946.452246, "helpdesk": 1658433308.494507, "admin_panel": null}'
WHERE id = 1;

但是，如果我们想要更新单个值，我们可以执行下面的操作：

UPDATE employees
SET
    systems_access = systems_access || jsonb_build_object('admin_panel', extract(epoch from now()))
WHERE id = 1;

运算符||可合并两个对象，并用右侧对象的键覆盖左侧对象上的键。并且，我们使用jsonb_build_object创建一个新对象，在等式中的任何一个值都可以是变量。

顶层对象：使用 BTREE 进行索引

我们有两种方法来索引顶层对象。此外，选择合适的索引也有一些注意事项。为单个值创建索引时，最好使用BTREE。

从一个简单的BTREE索引开始，假设我们想根据systems_access字段中的crm键来查询。对于此索引，我们可以显式声明字段，同时将该字段转换为float。

CREATE INDEX employees_systems_access_crm
ON employees
USING BTREE (((systems_access->>'crm')::float));

然后，当我们运行下面的查询时，它会使用到索引：

EXPLAIN SELECT
    *
FROM employees
WHERE
    (systems_access->>'crm')::float > extract('epoch' FROM now() - '3 day'::interval);

运行上述命令，您会看到输出中有一个Index cond，表示它使用了索引进行查找。如果您没有看到Index cond，则需要通过运行ANALYZE employees;来更新表的统计信息。

尝试运行下上述EXPLAIN命令。再尝试DROP INDEX employees_systems_access_crm，然后重新运行EXPLAIN。然后，重新创建索引并运行EXPLAIN。比较输出。现在，尝试将间隔从 3 天更改为 10 天 — 注意到它恢复为表扫描了吗？这是由于 PostgreSQL 优化器会使用表统计信息，来确定表扫描比索引扫描更快。我们使用的数据集相对较小，这意味着索引可能比表扫描慢。

顶层对象：使用 GIN 进行索引

当使用这些沙盒数据大小的 GIN 索引时，查询优化器并不总会选择使用索引。我们将逐步完成索引的创建过程，但要知道优化器还有自己的决策逻辑。对于性能，上面的BTREE方法是最可预测的索引方法。尽管如此，我们还是想展示 GIN 索引的工作原理。

如果我们想查询systems_access字段的任何值怎么办？那么，我们会希望为此使用 GIN 索引。

CREATE INDEX employees_systems_access ON employees USING GIN ((systems_access) jsonb_path_ops);

现在，让我们使用下面的EXPLAIN，看看是否可以将索引用作查询的一部分：

EXPLAIN SELECT
    *
FROM employees
WHERE
    systems_access @@ ('$.helpdesk > ' || extract('epoch' FROM now() - '3 day'::interval)::text)::jsonpath;

此查询可能不使用索引，因为我们需要更新表统计信息。为此，请运行ANALYZE employees;，然后重新运行上面的查询。

一旦它起作用，让我在这里提出一个警告，在索引创建的时候声明jsonb_path_ops，并在查询时使用@@运算符，对于索引的生效至关重要。使用GIN索引时，要匹配的运算符与要运行的比较要匹配上。例如，如果我们运行下面的查询，则不会使用索引：

EXPLAIN SELECT
    *
FROM employees
WHERE
    systems_access->>'helpdesk' = '1';

该示例的要点是，在使用 JSONB 时，构建索引和使用这些索引的查询，需要一些时间并理解。如果我们删除了在创建索引时指定的jsonb_path_ops，则使用@@的原始查询也将无法使用索引。此外，如果运算符的右侧包含两个值，例如{"helpdesk": 1, "crm": 1}，则它也无法使用索引。

结构化数据

关于数据结构（也叫 schema）的说明：您可能听说过 JSON 数据库的 “无结构” 一词。这是用词不当。所有应用程序都有定义的数据结构，问题是：您的应用程序软件栈中的哪一部分会引入数据结构？使用 PostgreSQL 表时，数据结构是在数据库级别引入的。通常，在使用 JSON 时，数据结构是在应用程序级别引入的。

在设计 JSON 数据时，考虑如何使用和查询数据非常重要。我们有许多方法来结构化地址数据，这些数据可以在 employees 表中找到。例如，我们可以使用下面的结构 — 请花几秒钟将其与我们上面使用的值进行比较。

{
    "home": {
        "street": "698 Candlewood Lane", 
        "city": "Cabot Cove", 
        "state": "Maine", 
        "country": "United States"
    }
}

上述语法对于我们的用例来说其实更简单，除非您想对其进行索引。为什么索引会更难？要提取值，键 （即home） 可能与预期不同。我们也可以使用顶层的地址，但这样就不能存储多个地址。

在设计 JSON 结构时，应该进行大量的思考和实验，其中一些最好通过经验来学习。其实，没有什么是“无结构” 的。