Android Room DB多表关联生成KV JSON字段遇阻求助

背景 #

我开发Android应用时用Room DB存取数据，需要关联多张表查询，且其中一列要返回键值对格式的JSON列表。之前在PHP+MySQL环境下，用下面的查询语句成功实现需求：

SELECT 
    t1.* ,
    JSON_MERGE_PATCH(
        JSON_OBJECTAGG(
            IFNULL(stu.id, '-1'), 
            IFNULL(stu.color, -1)
        ),
        JSON_OBJECTAGG(
            IFNULL(CASE t5.toOrder WHEN 1 THEN 5 WHEN 0 THEN 6 ELSE -1 END, '-1'), 
            IFNULL((SELECT color FROM `Table4` WHERE id = CASE t5.toOrder WHEN 1 THEN 5 WHEN 0 THEN 6 ELSE -1 END), -1)
        )
    )
    AS status 
    FROM ((((Table1 AS t1
    LEFT JOIN `Table2` AS t2 ON t2.itemId = t1.id) 
    LEFT JOIN `Table3` AS t3  ON t3.id = t2.orderId)
    LEFT JOIN `Table4` AS stu ON t3.statusId = stu.id)
    LEFT JOIN `Table5` AS t5 ON t1.id = t5.itemId)
    GROUP BY t1.id ORDER BY t1.`name`

该查询能正常返回如下格式的JSON字段：

{
"1":-2659,
"5":-749647,
"-1":-1
}

当前问题 #

我在Room DB（SQLite）中编写了对应的查询语句，在桌面数据库浏览器里运行正常，但在Room中status字段始终为空。我已经了解并启用了json1扩展，也使用了requery库，问题依然存在。

Room中使用的查询语句：

SELECT
    t1.*,
    json_patch(
        json_group_object(
            CAST(IFNULL(t4.id, '-1') as TEXT), 
            IFNULL(t4.color, -1)
        ),
        json_group_object(
            CAST(IFNULL(CASE t5.toOrder WHEN 1 THEN 5 WHEN 0 THEN 6 ELSE -1 END, '-1') as TEXT), 
            IFNULL((SELECT color FROM `Status` WHERE id = CASE t5.toOrder WHEN 1 THEN 5 WHEN 0 THEN 6 ELSE -1 END), -1)
        )
    )
    AS status
    FROM Tble1 AS t1
    LEFT JOIN `Table2` AS t2 ON t2.itemId = t1.id
    LEFT JOIN `Table3` AS t3 ON t3.id = t2.orderId
    LEFT JOIN `Table4` AS t4 ON t3.statusId = t4.id
    LEFT JOIN `Table5` AS t5 ON t1.id = t5.itemId
    GROUP BY t1.id ORDER BY t1.`name`

内容的提问来源于stack exchange，提问作者Samy K.