我正在使用sqlite3存储应用程序的数据。我需要在数据库上执行连接查询，以接收来自多个表的组合信息。问题是，我必须将tbl_assets的每行与tbl_users和tbl_permissions中的多行连接起来。
我认为查询结果需要是嵌套的json格式，但这似乎是不可能的。
为了帮助您理解，我在下面发布了我当前的**database、query、result和desired_result的简化示例。
我的database**中有三个表：

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

我使用下面的**query**来检索user_1的数据：

query = `
         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'
        `

这将为user_1返回特定的**result**：

result = [
  {
    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
  }
]

这是一个良好的开端，但还不足以满足我的需要。此外，我需要在结果中查看所有用户及其对返回的每个资产的（特定）权限。以下是**desired_result**的示例：

desired_result = [
  {
    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
                   }
                 ]
  }
]

我研究了一些选项来实现这一目标，但我不确定哪种方法最好，甚至不确定是否可行。（！）重要：结果结构不需要与我的示例中的结构完全相同，结果只需要包含上面desired_result中所示的信息。
我想到了以下几种选择：
1.修改我的查询-〉很遗憾我不知道如何修改
1.对result阵列中的每个资产运行额外查询-〉数据量大时性能差
1.重组我的数据库-〉影响我的代码，似乎很费力
我更愿意选择1.修改我的sql查询，以便在一个查询中获得我想要的结果。我的研究到目前为止还没有给我带来一个解决方案。我在SQL Server中找到了一些关于“FOR JSON AUTO”的东西，但这似乎对sqlite不起作用。如果能给我指明方向，我将非常感激。
如果你知道我想要的结果不能在一个查询中实现，我将不胜感激的评论停止在这个方向上寻找解决方案。
如果重组我的数据库将是一个很好的建议，我会很感激一个例子的结构。