查询 JSON

D1 内置支持查询和解析存储在数据库中的 JSON 数据。这使您能够：

• 查询存储的 JSON 对象内的路径——例如，直接提取命名键或数组索引的值，对于较大的 JSON 对象尤其有用。
• 在对象或数组中插入和/或替换值。
• 将 JSON 对象或数组的内容展开为多行——例如，作为 WHERE ... IN 谓词的一部分使用。
• 创建生成列，自动用您插入的 JSON 对象中的值填充。

在 D1 内直接解析 JSON 的最大好处之一是它可以直接减少数据库的往返（查询）次数。它减少了必须将 JSON 对象读入应用 (1)、解析、然后写回 (2) 的情况。

这使您能够更精确地查询数据，并减少应用需要额外解析和过滤的结果集。

JSON 数据在 D1 中存储为 TEXT 列。JSON 类型遵循与 D1 一般相同的类型转换规则，包括：

• JSON null 被视为 D1 NULL。
• JSON 数字被视为 INTEGER 或 REAL。
• 布尔值被视为 INTEGER 值：true 为 1，false 为 0。
• 对象和数组值作为 TEXT。

下表概述了 D1 内置的 JSON 函数及示例用法。

• json 参数占位符可以是 JSON 对象、数组、字符串、数字或 null 值。
• value 参数仅接受字符串字面量，并将输入视为字符串，即使它是格式良好的 JSON。此规则的例外是嵌套 json_* 函数时：外层（包装）函数会将内层（被包装）函数的返回值解释为 JSON。
• path 参数接受路径式遍历语法——例如，$ 引用顶层对象/数组，$.key1.key2 引用嵌套对象，$.key[2] 索引到数组。

D1 所基于的 SQLite JSON 扩展 ↗有更多用法示例。

JSON 函数在对非 JSON 和/或无效 JSON 数据操作时，将返回 malformed JSON 错误。D1 认为有效的 JSON 符合 RFC 7159 ↗。

在以下示例中，对字符串（非有效 JSON）调用 json_extract 将导致查询返回 malformed JSON 错误：

SELECT json_extract('not valid JSON: just a string', '$')

这将返回错误：

ERROR 9015: SQL engine error: query error: Error code 1: SQL error or missing database (malformed
  JSON)`

D1 对生成列的支持允许您创建基于其他列值（包括 JSON 数据的提取或计算值）动态生成的列。

这些列可以像任何其他列一样查询，并可以在其上定义索引。如果您有频繁查询和过滤的 JSON 数据，创建生成列和索引可以大幅提升查询性能。

例如，要定义基于较大 JSON 对象内值的列，使用 AS 关键字结合 JSON 函数 生成类型化列：

CREATE TABLE some_table (
    -- other columns omitted
    raw_data TEXT -- JSON: {"measurement":{"aqi":[21,42,58],"wind_mph":"13","location":"US-NY"}}
    location AS (json_extract(raw_data, '$.measurement.location')) STORED
)

请参阅生成列，了解更多关于如何生成列的信息。

在 D1 中有三种从 JSON 对象提取值的方式：

• json_extract() 函数——例如 json_extract(text_column_containing_json, '$.path.to.value)。
• -> 运算符，返回值的 JSON 表示。
• ->> 运算符，返回值的 SQL 表示。

-> 和 ->> 运算符的功能与 PostgreSQL 和 MySQL/MariaDB 中的相同运算符类似。

给定名为 sensor_reading 的列中的以下 JSON 对象，您可以直接从中提取值。

{
    "measurement": {
        "temp_f": "77.4",
        "aqi": [21, 42, 58],
        "o3": [18, 500],
        "wind_mph": "13",
        "location": "US-NY"
    }
}

-- Extract the temperature value
json_extract(sensor_reading, '$.measurement.temp_f')-- returns "77.4" as TEXT

-- Extract the maximum PM2.5 air quality reading
sensor_reading -> '$.measurement.aqi[3]' -- returns 58 as a JSON number

-- Extract the o3 (ozone) array in full
sensor_reading -\-> '$.measurement.o3' -- returns '[18, 500]' as TEXT

有两种方式获取 JSON 数组的长度：

1. 直接调用 json_array_length(value)
2. 调用 json_array_length(value, path) 指定对象或外层数组内数组的路径。

例如，给定存储在名为 login_history 的列中的以下 JSON 对象，您可以直接获取最近登录次数：

{
    "user_id": "abc12345",
    "previous_logins": ["2023-03-31T21:07:14-05:00", "2023-03-28T08:21:02-05:00", "2023-03-28T05:52:11-05:00"]
}

json_array_length(login_history, '$.previous_logins') --> returns 3 as an INTEGER

您还可以在更复杂的查询中将 json_array_length 用作谓词——例如 WHERE json_array_length(some_column, '$.path.to.value') >= 5。

您可以使用 json_insert() 向现有 JSON 对象或数组插入值。例如，如果 users 表中有一个名为 login_history 的 TEXT 列，包含以下对象：

{"history": ["2023-05-13T15:13:02+00:00", "2023-05-14T07:11:22+00:00", "2023-05-15T15:03:51+00:00"]}

要向 login_history 列内的 history 数组添加新时间戳，编写类似以下的查询：

UPDATE users
SET login_history = json_insert(login_history, '$.history[#]', '2023-05-15T20:33:06+00:00')
WHERE user_id = 'aba0e360-1e04-41b3-91a0-1f2263e1e0fb'

向 json_insert 提供三个参数：

1. 包含要修改的 JSON 的列名。
2. 对象内要修改的键的路径。
3. 要插入的 JSON 值。使用 [#] 告诉 json_insert 追加到数组末尾。

要替换现有值，使用 json_replace()，如果已存在键值对则会覆盖。无论值是否已存在都要设置值，使用 json_set()。

使用 json_each 将数组展开为多行。这在编写针对多个值的 WHERE column IN (?) 查询时很有用。例如，如果您想按整数 id 更新用户列表，使用 json_each 返回一个表，每个值作为名为 value 的列：

UPDATE users
SET last_audited = '2023-05-16T11:24:08+00:00'
WHERE id IN (SELECT value FROM json_each('[183183, 13913, 94944]'))

这将仅从 json_each 返回的表中提取 value 列，每行代表您作为数组传入的用户 ID。

json_each 实际上返回一个包含多列的表，最相关的列包括：

• key - 键（或索引）。
• value - 由 json_each 解析的每个元素的字面值。
• type - 值的类型：null、true、false、integer、real、text、array 或 object 之一。
• fullkey - 元素的完整路径：例如数组中第二个元素为 $[1]，嵌套对象为 $.path.to.key。
• path - 顶层路径——fullkey 为 $[0] 的元素的 path 为 $。

在此示例中，SELECT * FROM json_each('[183183, 13913, 94944]') 将返回类似以下的表：

key|value|type|id|fullkey|path
0|183183|integer|1|$[0]|$
1|13913|integer|2|$[1]|$
2|94944|integer|3|$[2]|$

您可以在 Worker 中通过创建语句并使用 JSON.stringify 将数组作为绑定参数传递，将 json_each 与 D1 Workers Binding API 配合使用：

const stmt = context.env.DB
    .prepare("UPDATE users SET last_audited = ? WHERE id IN (SELECT value FROM json_each(?1))")
const resp = await stmt.bind(
    "2023-05-16T11:24:08+00:00",
    JSON.stringify([183183, 13913, 94944])
    ).run()

这将仅更新 users 表中 id 与提供的三个 ID 之一匹配的行。