如何在生成列中使用JSON函数？（含SQL表结构与查询示例）

背景信息 #

表结构 #

CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    json_data TEXT
);

json_data列格式 #

[
  {"key": "foo", "date": "2023-01-01"},
  {"key": "bar", "date": "2023-01-02"}
]

现有查询（获取第一个key为"bar"的元素日期） #

SELECT (
    SELECT json_extract(value, '$.date')
    FROM my_table,
         json_each(my_table.json_data) AS each
    WHERE json_extract(each.value, '$.key') = 'bar'
    ORDER BY each.rowid
    LIMIT 1
) AS first_bar_date
FROM my_table;

核心问题解答 #

可以在SQLite的生成列中使用JSON函数，但需注意两个关键限制：

• 仅支持确定性函数：json_extract()、json_valid()、json_type()等JSON函数属于确定性函数（输入相同则输出固定），可直接用于生成列；但json_each()这类返回多行结果的函数不能直接使用，因为生成列要求返回单个标量值。

• 多行逻辑需转为单行表达式：你当前的查询依赖json_each()遍历数组，这种多行逻辑无法直接放入生成列，需要调整为单行逻辑。以下是两种可行的实现方式：

方式1：使用JSON路径（SQLite 3.38.0+支持） #

利用JSON路径的过滤语法直接定位目标元素，写法更简洁：

CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    json_data TEXT,
    first_bar_date TEXT GENERATED ALWAYS AS (
        json_extract(json_data, '$[*] ? (@.key == "bar")')[0]
    ) STORED
);

方式2：使用单行子查询（兼容更早版本） #

将原查询的子查询直接嵌入生成列定义，确保子查询仅返回单个值：

CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    json_data TEXT,
    first_bar_date TEXT GENERATED ALWAYS AS (
        (SELECT json_extract(value, '$.date')
         FROM json_each(json_data)
         WHERE json_extract(value, '$.key') = 'bar'
         ORDER BY rowid
         LIMIT 1)
    ) STORED
);

内容的提问来源于stack exchange，提问作者Acorn