SQLite实现资产表与多用户权限关联的嵌套结果查询方案

数据库表结构 #

-- tbl_users
id      | name
------------------
1       | user_1
2       | user_2

-- tbl_assets
id      | title
------------------
3       | asset_1
4       | asset_2

-- tbl_permissions
user_id | asset_id | can_read | can_edit | can_delete
------------------------------------------------------
1       | 3        | 1        | 1        | 0
1       | 4        | 1        | 1        | 1
2       | 4        | 1        | 1        | 1

当前查询及结果 #

查询user_1数据的SQL语句：

SELECT a.*, p.can_read, p.can_edit, p.can_delete
FROM tbl_users u
INNER JOIN tbl_permissions p
ON u.id = p.user_id
INNER JOIN tbl_assets a
ON a.id = p.asset_id
WHERE u.id = '1'

返回结果：

[
  {
    "id": 3,
    "title": "asset_1",
    "can_read": 1,
    "can_edit": 1,
    "can_delete": 0
  },
  {
    "id": 4,
    "title": "asset_2",
    "can_read": 1,
    "can_edit": 1,
    "can_delete": 1
  }
]

期望结果 #

希望返回每个资产对应所有用户权限的嵌套JSON：

[
  {
    "id": 3,
    "title": "asset_1",
    "permissions": [
      {
        "user_id": 1,
        "can_read": 1,
        "can_edit": 1,
        "can_delete": 0
      }
    ]
  },
  {
    "id": 4,
    "title": "asset_2",
    "permissions": [
      {
        "user_id": 1,
        "can_read": 1,
        "can_edit": 1,
        "can_delete": 1
      },
      {
        "user_id": 2,
        "can_read": 1,
        "can_edit": 1,
        "can_delete": 1
      }
    ]
  }
]

需求与方案偏好 #

需要将tbl_assets每行与tbl_users、tbl_permissions的多行数据关联，生成嵌套JSON结果。考虑了三种方案：

• 修改现有查询（优先选择，希望单条SQL实现）
• 对每个资产执行额外查询（大数据量下性能差）
• 重构数据库（改动大，影响现有代码）

已知SQL Server的FOR JSON AUTO不适用于SQLite，若单查询无法实现请告知；若建议重构数据库，请给出示例结构。

解决方案 #

单条SQL实现（SQLite 3.33.0+）

SQLite 3.33.0及以上版本支持JSON_GROUP_ARRAY和JSON_OBJECT聚合函数，可直接生成嵌套JSON结构：

SELECT 
  a.id,
  a.title,
  JSON_GROUP_ARRAY(
    JSON_OBJECT(
      'user_id', p.user_id,
      'can_read', p.can_read,
      'can_edit', p.can_edit,
      'can_delete', p.can_delete
    )
  ) AS permissions
FROM tbl_assets a
LEFT JOIN tbl_permissions p ON a.id = p.asset_id
GROUP BY a.id, a.title;

说明：

• JSON_OBJECT将每条权限记录转换为JSON对象
• JSON_GROUP_ARRAY将同一资产的所有权限对象聚合为数组
• LEFT JOIN确保即使无权限的资产也会被返回（若不需要可改为INNER JOIN）

如果只需查询特定用户关联的资产（如原查询中的user_id=1），可调整为：

SELECT 
  a.id,
  a.title,
  JSON_GROUP_ARRAY(
    JSON_OBJECT(
      'user_id', p.user_id,
      'can_read', p.can_read,
      'can_edit', p.can_edit,
      'can_delete', p.can_delete
    )
  ) AS permissions
FROM tbl_assets a
INNER JOIN tbl_permissions p ON a.id = p.asset_id
WHERE p.user_id = 1
GROUP BY a.id, a.title;

低版本SQLite替代方案

若你的SQLite版本低于3.33.0，无法使用上述JSON聚合函数，只能在客户端代码中二次处理：

1. 查询所有资产与关联权限的平级结果
2. 遍历结果，按资产ID分组，将权限数据组装为嵌套数组

数据库重构建议

当前的三表结构（用户-权限关联表-资产）是标准的多对多关系设计，已经是最优方案，无需重构。强行改动会增加代码适配成本，不建议执行。

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