json1 extention with Room db

I'm working on an android app and using Room db to store and get data, I need to get some data from multible tables where one of result columns must contain a list of a kv list the same requirment were achived on a php server with mysql by the following query

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`

which works just fine and I'm able to get that column as json like this

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

now I'm trying to implement the same query whit Room db sqlite but with no luck

this is what I've tried (works fine on a desktop database browser but not with Room db)

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`

I know about the json1 extention but even after using the requery library still get the status field as empty can anyone help please

Solution

Actually, it was an undeclared error, Room was ignoring the "status" field because I was using the @Ignore annotation on it. so, changing this

@Ignore var status: String

to this

@ColumnInfo(name = "status") var status: String

solved the issue and I'm able to use the json1 extension and functions with Room and requery library

note that I only added the implementation to gradle

implementation 'io.requery:sqlite-android:3.31.0'

and the open helper to the Room database biulder

.openHelperFactory(RequerySQLiteOpenHelperFactory())

and not using anything else from the requery library.