SQLite JSON1子查询技术咨询：Android应用SQLCipher场景问题

嘿，很高兴JSON1扩展刚好能匹配你的需求！既然SQLite官方文档没覆盖子查询和JSON1结合的场景，我就结合你提到的测试用例，给你具体的实现指导——先假设你已经创建了测试表并插入了两行数据，比如我们定义一个名为app_records的表，包含id主键和json_data存储JSON内容的字段：

-- 创建测试表
CREATE TABLE app_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    json_data TEXT
);

-- 插入两行测试数据
INSERT INTO app_records (json_data) VALUES
('{"user_id": 101, "tags": ["android", "sqlcipher", "json"], "settings": {"dark_mode": true, "notifications": false}}'),
('{"user_id": 102, "tags": ["kotlin", "sqlite", "json1"], "settings": {"dark_mode": false, "notifications": true}}');

接下来我会针对几种常见的子查询场景给出实现方案，你可以根据自己的实际需求调整：

1. 子查询筛选包含特定JSON属性值的行 #

比如你想找出所有settings里dark_mode为true的记录，同时用子查询确认tags数组里包含指定标签：

SELECT * FROM app_records
WHERE json_extract(json_data, '$.settings.dark_mode') = 1
AND EXISTS (
    -- 子查询：检查tags数组中是否存在"android"标签
    SELECT 1 FROM json_each(json_data, '$.tags')
    WHERE value = 'android'
);

这里json_each()会把JSON数组拆分成多行数据，子查询通过EXISTS判断是否存在符合条件的元素。

2. 从JSON数组提取数据并关联子查询 #

如果你需要把JSON数组中的元素和主表数据关联，比如提取每个用户的所有标签，并关联用户ID：

SELECT
    ar.id,
    ar.user_id,
    tag.value AS tag_name
FROM (
    -- 子查询：先提取主表的基础数据和用户ID
    SELECT
        id,
        json_extract(json_data, '$.user_id') AS user_id,
        json_data
    FROM app_records
) AS ar,
json_each(ar.json_data, '$.tags') AS tag;

这个查询会把每个用户的标签拆分成独立行，方便后续的统计或筛选操作。

3. 子查询统计JSON数组中的符合条件元素数量 #

比如你想统计每个记录中，标签包含"sql"关键词的数量：

SELECT
    id,
    json_extract(json_data, '$.user_id') AS user_id,
    (
        -- 子查询：统计符合条件的标签数量
        SELECT COUNT(*) FROM json_each(json_data, '$.tags')
        WHERE value LIKE '%sql%'
    ) AS sql_tag_count
FROM app_records;

这里子查询用COUNT(*)统计匹配的数组元素，直接作为主查询的一个字段返回。

关键注意点 #

• 确保你的SQLCipher版本确实支持JSON1扩展（你已经确认过这点，很好！）
• 使用json_extract()时，路径表达式要准确，比如$.user_id表示根节点下的user_id属性
• json_each()是处理JSON数组的核心函数，它能把数组转成关系型表结构，方便子查询操作

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