Flutter中基于SQLite实现REST API风格嵌套JSON查询的方法咨询

SQLite原生支持情况 #

SQLite 从3.33.0版本开始新增了json_group_array()、json_object()等JSON处理函数，完全支持直接查询输出嵌套JSON结构，不需要在应用层做额外的数据拼装。
如果你的Flutter应用需要兼容搭载低版本SQLite的旧系统，可以引入sqlite3_flutter_libs依赖内置最新版SQLite，即可使用上述JSON相关能力。

对应查询语句实现 #

1. 查询客户列表嵌套关联交易记录 #

SELECT 
  json_object(
    'id', c.id,
    'name', c.name,
    'transactions', json_group_array(
      json_object(
        'id', t.id,
        'type', t.type
      )
    )
  ) AS customer_json
FROM customers c
LEFT JOIN transactions t ON c.id = t.customer
GROUP BY c.id;

执行后每一行的customer_json字段就是符合要求的嵌套结构JSON字符串，可直接解析为Dart对象使用。如果要排除没有交易记录的客户，把LEFT JOIN改为INNER JOIN即可。

2. 查询交易列表嵌套关联客户信息 #

SELECT 
  json_object(
    'id', t.id,
    'type', t.type,
    'customer', json_object(
      'id', c.id,
      'name', c.name
    )
  ) AS transaction_json
FROM transactions t
INNER JOIN customers c ON t.customer = c.id;

该场景无需分组，单条交易对应唯一关联客户，直接嵌套构造customer对象即可。

低版本SQLite不支持时的最优解决方案 #

如果无法升级SQLite版本，优先选择在Dart层做数据组装，该方案兼容性最好、性能损耗极低，具体实现逻辑如下：

• 客户列表嵌套交易场景：先执行两表联合查询拿到扁平化结果集，遍历结果时对客户ID去重保留唯一客户对象，同时将当前行的交易数据塞入对应客户的交易数组中，仅需一次数据库IO即可完成数据组装。
• 交易列表嵌套客户场景：直接执行两表联合查询，遍历每一行结果时，把客户相关字段单独摘出构造为客户对象，嵌套到交易对象中即可。

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