json1 extention with Room db

Question

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

Answer 1

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.

Answer 2

Unless you are using and Android version 34 or greater, then the JSON functions are not included in the SQLite version.

As per:-

The JSON functions and operators are built into SQLite by default, as of SQLite version 3.38.0 (2022-02-22). They can be omitted by adding the -DSQLITE_OMIT_JSON compile-time option. Prior to version 3.38.0, the JSON functions were an extension that would only be included in builds if the -DSQLITE_ENABLE_JSON1 compile-time option was included. In other words, the JSON functions went from being opt-in with SQLite version 3.37.2 and earlier to opt-out with SQLite version 3.38.0 and later.

References

• https://www.sqlite.org/json1.html#compiling_in_json_support
• https://developer.android.com/reference/android/database/sqlite/package-summary.html

and

• Version of SQLite used in Android?