javaandroidjsonsqliteandroid-sqlite

SQlite for Android not working with json_group_array/json_object

Hey all I am trying to get this SQlite query to work in my Android app but for some reason its giving me the error of:

no such function: json_object (code 1): , while compiling

But it works in my DB Browser when I run the query?

My query is this:

SELECT json_group_array( 
    json_object(
        'name', name, 
        'ip', ip,
        'mac', mac,
        'token', token
    )
) 
FROM tvs

enter image description here

And the output is:

[{"name":"lr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},

{"name":"mbr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},

{"name":"jr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},

{"name":"tr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},

{"name":"dsr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},

{"name":"lr2","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"},

{"name":"lr","ip":"192.168.1.0","mac":"00:00:00:00","token":"na"}]

Putting that query into my Android app:

Cursor cursor;

try {
   cursor = db.rawQuery(
      "SELECT " +
           "json_group_array(" +
                "json_object(" +
                     "'name', name," +
                     "'ip', ip," +
                     "'mac', mac," +
                     "'token', token" +
                ")" +
           ")" +
      "FROM " +
           "tvs", null);
  cursor.moveToFirst();
  String result = cursor.getString(0);
} catch (JsonProcessingException e) {
  throw new RuntimeException(e);
}

The error shows up when on the db.rawQuery line.

Does Androids SQlite not have support for json_group_array/json_object?

Solution

  • Does Androids SQlite not have support for json_group_array/json_object?

    Based upon

    The JSON functions and operators are built into SQLite by default, as of SQLite version 3.38.0 (2022-02-22).

    Then support of the functions would only be for API 34+ (Version 14 or U) according to Version of SQLite used in Android?

    Android versions of the built-in SQLite are historically behind the available versions of SQLite.