How to delete a specific item from a list in room db

i want to delete a single item from a list in room db, i've gone through few docs where it used json_remove() function within the SQL query in the @Query but it doesn't work and throws error;

this is how dao looks:

@Entity(tableName = "data_table")
@Serializable
data class DataTable(
    val name: String,
    @PrimaryKey
    val url: String,
    val addon: String
)


@Entity(tableName = "entries_table")
@Serializable
@TypeConverters(EntriesTypeConverter::class)
data class EntriesTable(
    @PrimaryKey
    val name: String,
    @TypeConverters(EntriesTypeConverter::class)
    val data: List<DataTable>,
)

now my question is that how can i remove a specific item from the data in EntriesTable based on url which is in theDataTable

i was using this query statement, i'm new to sql things, i've gone thorough example from sqlite.org and from chatgpt, all i pulled was the following query, which doesnt work:

@Query("UPDATE entries_table SET links = json_remove(data, (SELECT json_extract(data, '$[webURL = :url]') FROM entries_table WHERE name = :name)) WHERE name = :name")

how can i solve this, thankyou:)

If you use a Type Converter to store a list that actual data stored is a representation of the list as a single value. SQLite (and therefore Room) does not know about individual list items rather the column where the data is stored is just a single all encompassing value.

Typically such a Type Converter will be a JSON String representation of the data with separators and names allowing the list object to be reconstructed.

A DataTables name will never equate to a column, rather it would be part of a column so you would have to use either the SQLite instr or a LIKE with a mask to locate the row or rows for the WHERE clause of the SQL.

• e.g. UPDATE .... WHERE instr(data,:name) or UPDATE .... WHERE data LIKE('*'||:name||'*').
• .... SET links is going to be problematic as there is, according to your EntriesTable class, there is no field and thus column named links.
• both instr and the LIKE with a mask will be quite resource expensive, and more so the longer the list.

Alternative Approach

I would suggest, seeing that you have two tables (i.e. two @Entity annotated classes) that you instead utilise the relational aspect of the database which would easily facilitate manipulation of DataTables per EntriesTable i.e. the data field/column.

With this approach instead of storing the same data in both tables (if DataTable is in fact a table) you have a table for the entries and a table for the datas, the latter including a link (reference/map) from a data_table row to it's parent entries_table row.

In the simplest form, the entries_table could be:-

@Entity(tableName = "entries_table")
data class EntriesTable(
    @PrimaryKey
    val name: String
)

• i.e. the list will be the related data_table rows

In the simplest form data_table could be:-

@Entity
data class DataTable(
    val name: String,
    @PrimaryKey
    val url: String,
    val addon: String,
    @ColumnInfo(index = true)
    val entries_reference: String
)

• note the added field entries_reference, which for access efficiency is indexed

However, as a row in the data_table that does not have a reference/link to a parent would likely be a waste it might be an idea to include enforcement of valid links (what is often termed as referential integrity). Thus you may well wish to include Foreign Key constraints that basically say that a row in the data_table MUST have a parent. As such the DataTable class could be:-

@Entity(
    tableName = "data_table",
    foreignKeys = [
        ForeignKey(
            entity = EntriesTable::class,
            parentColumns = ["name"],
            childColumns = ["entries_reference"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class DataTable(
    val name: String,
    @PrimaryKey
    val url: String,
    val addon: String,
    @ColumnInfo(index = true)
    val entries_reference: String
)

• The onDelete will delete the children of the parent automatically if the parent is deleted.
• The onUpdate will automatically update the value of the entries_reference if the name of the parent is changed.
• these two are perhaps the most useful of the available ON ACTION options.

With this structure the deletion of a data_table row effectively updates the list (as will be demonstrated later). This being the task that you wish to accomplish according to your question.

You would very likely want to retrieve entries_table rows along with the respective list of data_tables, for this you could have a POJO such as:-

/* POJO for getting Entries with the related children (data_table rows) */
data class EntryWithData(
    @Embedded
    val entriesTable: EntriesTable,
    @Relation(
        entity = DataTable::class,
        parentColumn = "name",
        entityColumn = "entries_reference"
    )
    val dataTableList: List<DataTable>
)

The following are DAO functions, including 2 delete functions that could be used to accomplish what the task if the question (aka remove a data_table from a list of data_tables):-

@Dao
interface AllDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(dataTable: DataTable): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(entriesTable: EntriesTable): Long
    /* Convenience Deletes NOTE room uses the Primary Key value (url) to determine what to delete (see working demo) */
    @Delete
    fun delete(entriesTable: EntriesTable): Int
    /* Delete according to provided url */
    /* NOTE UPDATE to remove is irrelevant as an EntriesTable will have a List of 0 or more DataTable rows */
    @Delete
    fun delete(dataTable: DataTable): Int
    @Query("DELETE FROM data_table WHERE url=:url")
    fun deleteByUrl(url: String)
    /* Query to get all DataTables */
    @Query("SELECT * FROM data_table")
    fun getAllDataTables(): List<DataTable>
    /* Query to get all EntryWithData objects (Each EntriesTable Object with it's list of DataTables) */
    @Transaction
    @Query("SELECT * FROM entries_table")
    fun getAllEntriesWithRelatedDataTableList(): List<EntryWithData>
}

Demonstration

To facilitate demonstration of the suggested structure a simple @Database annotated class (noting that for brevity the main thread will be used):-

@Database(entities = [DataTable::class,EntriesTable::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAOs(): AllDAOs

    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance=Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

To actually demonstrate then the following activity code:-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDAOs()

        /* use the database to add some data, and then delete data, writing the contents of the tables at various stages */
        doSomeDbWork()
    }

    fun doSomeDbWork() {
        /* Add 4 EntriesTable rows */
        dao.insert(EntriesTable("Entry1"))
        dao.insert(EntriesTable("Entry2"))
        dao.insert(EntriesTable("Entry3"))
        dao.insert(EntriesTable("Entry4"))

        /* Add related DataTable rows, 3 related to Entry1, 3 related to Entry 2, 1 related to Entry3 and 1 related to Entry 4 */
        dao.insert(DataTable("DT01","dt01url","addonA","Entry1"))
        dao.insert(DataTable("DT02","dt02url","addonB","Entry1"))
        dao.insert(DataTable("DT03","dt03url","addonC","Entry1"))
        dao.insert(DataTable("DT04","dt04url","addonA","Entry2"))
        dao.insert(DataTable("DT05","dt05url" ,"addon1","Entry2"))
        dao.insert(DataTable("DT06","dt06url","addonD","Entry2"))
        dao.insert(DataTable("DT07","dt07url","addonE","Entry3"))
        dao.insert(DataTable("DT08","dt08url","addonF","Entry4"))

        /* Show the initial data */
        logEntriesWithDataTableLists("STAGE1")

        /* example of deleting non existent datatable */
        dao.delete(DataTable("does not matter","not a saved url","again does not matter","again does not matter"))
        logEntriesWithDataTableLists("STAGE2")

        /* example of deleting existing datatable shows that all that matters is the Primary key value as that is what Room uses to ascertain what to delete */
        dao.delete(DataTable("does not matter","dt07url","again does not matter","again does not matter"))
        logEntriesWithDataTableLists("STAGE3")
        /* example of alternative deleting by url */
        dao.deleteByUrl("dt01url")
        logEntriesWithDataTableLists("STAGE4")
        /* example of deleting an Entry and the deletion being cascaded to the children of the entry */
        dao.delete(EntriesTable("Entry3"))
        logEntriesWithDataTableLists("STAGE5")
    }

    fun logEntriesWithDataTableLists(tagSuffix: String){
        for(ewd in dao.getAllEntriesWithRelatedDataTableList()) {
            val sb=StringBuilder()
            for (dt in ewd.dataTableList) {
                sb.append("\n\tDT name = ${dt.name} URL=${dt.url} ADDON=${dt.addon} REFERS TO PARENT ENTRY ${dt.entries_reference}")
            }
            Log.d("DBINFO_${tagSuffix}","Entry is ${ewd.entriesTable.name} it has ${ewd.dataTableList.size} related DT's. They are:-${sb}")
        }
        logAllDatatables(tagSuffix)
    }

    fun logAllDatatables(tagSuffix: String) {
        for (dt in dao.getAllDataTables()) {
            Log.d("DBINFO_DT_$tagSuffix","DT is ${dt.name} URL is ${dt.url} ADDON is ${dt.addon} REFERS TO PARENT ENTRY ${dt.entries_reference}")
        }
    }
}

Result

When run (for the first time) then the log includes:-

STAGE1

This is after the 4 rows have been added to the entries_table and the 8 rows have been added to the data_table table.

Of the 8 data_table rows 3 are related to Entry1, 2 are related to Entry2 and 1 each are related to Entry 3 and Entry 4.

2023-06-19 14:51:48.532 D/DBINFO_STAGE1: Entry is Entry1 it has 3 related DT's. They are:-
    	DT name = DT01 URL=dt01url ADDON=addonA REFERS TO PARENT ENTRY Entry1
    	DT name = DT02 URL=dt02url ADDON=addonB REFERS TO PARENT ENTRY Entry1
    	DT name = DT03 URL=dt03url ADDON=addonC REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.532 D/DBINFO_STAGE1: Entry is Entry2 it has 3 related DT's. They are:-
    	DT name = DT04 URL=dt04url ADDON=addonA REFERS TO PARENT ENTRY Entry2
    	DT name = DT05 URL=dt05url ADDON=addon1 REFERS TO PARENT ENTRY Entry2
    	DT name = DT06 URL=dt06url ADDON=addonD REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.532 D/DBINFO_STAGE1: Entry is Entry3 it has 1 related DT's. They are:-
    	DT name = DT07 URL=dt07url ADDON=addonE REFERS TO PARENT ENTRY Entry3
2023-06-19 14:51:48.532 D/DBINFO_STAGE1: Entry is Entry4 it has 1 related DT's. They are:-
    	DT name = DT08 URL=dt08url ADDON=addonF REFERS TO PARENT ENTRY Entry4
2023-06-19 14:51:48.534 D/DBINFO_DT_STAGE1: DT is DT01 URL is dt01url ADDON is addonA REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.534 D/DBINFO_DT_STAGE1: DT is DT02 URL is dt02url ADDON is addonB REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.534 D/DBINFO_DT_STAGE1: DT is DT03 URL is dt03url ADDON is addonC REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.534 D/DBINFO_DT_STAGE1: DT is DT04 URL is dt04url ADDON is addonA REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.534 D/DBINFO_DT_STAGE1: DT is DT05 URL is dt05url ADDON is addon1 REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.534 D/DBINFO_DT_STAGE1: DT is DT06 URL is dt06url ADDON is addonD REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.535 D/DBINFO_DT_STAGE1: DT is DT07 URL is dt07url ADDON is addonE REFERS TO PARENT ENTRY Entry3
2023-06-19 14:51:48.535 D/DBINFO_DT_STAGE1: DT is DT08 URL is dt08url ADDON is addonF REFERS TO PARENT ENTRY Entry4

STAGE2

This is after the attempt to delete row(s) that do not exist in the data_table i.e. the specified url is not a row.

• note as will be shown later, the other values of the passed DataTable class are irrelevant.
  As such the resultant data is identical to STAGE1.

:-

2023-06-19 14:51:48.542 D/DBINFO_STAGE2: Entry is Entry1 it has 3 related DT's. They are:-
    	DT name = DT01 URL=dt01url ADDON=addonA REFERS TO PARENT ENTRY Entry1
    	DT name = DT02 URL=dt02url ADDON=addonB REFERS TO PARENT ENTRY Entry1
    	DT name = DT03 URL=dt03url ADDON=addonC REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.543 D/DBINFO_STAGE2: Entry is Entry2 it has 3 related DT's. They are:-
    	DT name = DT04 URL=dt04url ADDON=addonA REFERS TO PARENT ENTRY Entry2
    	DT name = DT05 URL=dt05url ADDON=addon1 REFERS TO PARENT ENTRY Entry2
    	DT name = DT06 URL=dt06url ADDON=addonD REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.543 D/DBINFO_STAGE2: Entry is Entry3 it has 1 related DT's. They are:-
    	DT name = DT07 URL=dt07url ADDON=addonE REFERS TO PARENT ENTRY Entry3
2023-06-19 14:51:48.543 D/DBINFO_STAGE2: Entry is Entry4 it has 1 related DT's. They are:-
    	DT name = DT08 URL=dt08url ADDON=addonF REFERS TO PARENT ENTRY Entry4
2023-06-19 14:51:48.546 D/DBINFO_DT_STAGE2: DT is DT01 URL is dt01url ADDON is addonA REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.546 D/DBINFO_DT_STAGE2: DT is DT02 URL is dt02url ADDON is addonB REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.546 D/DBINFO_DT_STAGE2: DT is DT03 URL is dt03url ADDON is addonC REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.547 D/DBINFO_DT_STAGE2: DT is DT04 URL is dt04url ADDON is addonA REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.547 D/DBINFO_DT_STAGE2: DT is DT05 URL is dt05url ADDON is addon1 REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.547 D/DBINFO_DT_STAGE2: DT is DT06 URL is dt06url ADDON is addonD REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.547 D/DBINFO_DT_STAGE2: DT is DT07 URL is dt07url ADDON is addonE REFERS TO PARENT ENTRY Entry3
2023-06-19 14:51:48.547 D/DBINFO_DT_STAGE2: DT is DT08 URL is dt08url ADDON is addonF REFERS TO PARENT ENTRY Entry4

STAGE3

Stage 3 is the deletion of a known/existing data_table row, i.e. the row that has the url value of dt07url (the 7th inserted data_table row which has Entry3 as it's parent).

• Note how Entry3 now has 0 related data_tables in it's list as per D/DBINFO_STAGE3: Entry is Entry3 it has 0 related DT's. i.e. the list, which was just the 1 item, has been updated to be 0 items.

• also note that even though the other fields of the passed DataTable are obviously incorrect, that the row is in fact deleted. i.e. confirming/demonstrating that it is just the PrimaryKey that is used to ascertain the row to be deleted.

:-

2023-06-19 14:51:48.554 D/DBINFO_STAGE3: Entry is Entry1 it has 3 related DT's. They are:-
    	DT name = DT01 URL=dt01url ADDON=addonA REFERS TO PARENT ENTRY Entry1
    	DT name = DT02 URL=dt02url ADDON=addonB REFERS TO PARENT ENTRY Entry1
    	DT name = DT03 URL=dt03url ADDON=addonC REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.555 D/DBINFO_STAGE3: Entry is Entry2 it has 3 related DT's. They are:-
    	DT name = DT04 URL=dt04url ADDON=addonA REFERS TO PARENT ENTRY Entry2
    	DT name = DT05 URL=dt05url ADDON=addon1 REFERS TO PARENT ENTRY Entry2
    	DT name = DT06 URL=dt06url ADDON=addonD REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.555 D/DBINFO_STAGE3: Entry is Entry3 it has 0 related DT's. They are:-
2023-06-19 14:51:48.555 D/DBINFO_STAGE3: Entry is Entry4 it has 1 related DT's. They are:-
    	DT name = DT08 URL=dt08url ADDON=addonF REFERS TO PARENT ENTRY Entry4
2023-06-19 14:51:48.561 D/DBINFO_DT_STAGE3: DT is DT01 URL is dt01url ADDON is addonA REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.562 D/DBINFO_DT_STAGE3: DT is DT02 URL is dt02url ADDON is addonB REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.562 D/DBINFO_DT_STAGE3: DT is DT03 URL is dt03url ADDON is addonC REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.562 D/DBINFO_DT_STAGE3: DT is DT04 URL is dt04url ADDON is addonA REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.562 D/DBINFO_DT_STAGE3: DT is DT05 URL is dt05url ADDON is addon1 REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.562 D/DBINFO_DT_STAGE3: DT is DT06 URL is dt06url ADDON is addonD REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.562 D/DBINFO_DT_STAGE3: DT is DT08 URL is dt08url ADDON is addonF REFERS TO PARENT ENTRY Entry4

STAGE4

Stage 4 deletes another row from the data_table, this time using the alternative delete function which is an @Query that takes just the url as it's parameter as opposed to the convenience @Delete which expects an object as it's parameter.

• Note that the disadvantage with this approach is that the number of affected (deleted) rows is not returned.

:-

2023-06-19 14:51:48.567 D/DBINFO_STAGE4: Entry is Entry1 it has 2 related DT's. They are:-
    	DT name = DT02 URL=dt02url ADDON=addonB REFERS TO PARENT ENTRY Entry1
    	DT name = DT03 URL=dt03url ADDON=addonC REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.567 D/DBINFO_STAGE4: Entry is Entry2 it has 3 related DT's. They are:-
    	DT name = DT04 URL=dt04url ADDON=addonA REFERS TO PARENT ENTRY Entry2
    	DT name = DT05 URL=dt05url ADDON=addon1 REFERS TO PARENT ENTRY Entry2
    	DT name = DT06 URL=dt06url ADDON=addonD REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.567 D/DBINFO_STAGE4: Entry is Entry3 it has 0 related DT's. They are:-
2023-06-19 14:51:48.567 D/DBINFO_STAGE4: Entry is Entry4 it has 1 related DT's. They are:-
    	DT name = DT08 URL=dt08url ADDON=addonF REFERS TO PARENT ENTRY Entry4
2023-06-19 14:51:48.570 D/DBINFO_DT_STAGE4: DT is DT02 URL is dt02url ADDON is addonB REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.570 D/DBINFO_DT_STAGE4: DT is DT03 URL is dt03url ADDON is addonC REFERS TO PARENT ENTRY Entry1
2023-06-19 14:51:48.570 D/DBINFO_DT_STAGE4: DT is DT04 URL is dt04url ADDON is addonA REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.570 D/DBINFO_DT_STAGE4: DT is DT05 URL is dt05url ADDON is addon1 REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.570 D/DBINFO_DT_STAGE4: DT is DT06 URL is dt06url ADDON is addonD REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.570 D/DBINFO_DT_STAGE4: DT is DT08 URL is dt08url ADDON is addonF REFERS TO PARENT ENTRY Entry4

STAGE5

Stage 5 demonstrates the onDelete CASCADE option. The entries_table Entry1 row is deleted and as can be seen so are the 2 remain data_table rows.

2023-06-19 14:51:48.576 D/DBINFO_STAGE5: Entry is Entry2 it has 3 related DT's. They are:-
    	DT name = DT04 URL=dt04url ADDON=addonA REFERS TO PARENT ENTRY Entry2
    	DT name = DT05 URL=dt05url ADDON=addon1 REFERS TO PARENT ENTRY Entry2
    	DT name = DT06 URL=dt06url ADDON=addonD REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.576 D/DBINFO_STAGE5: Entry is Entry3 it has 0 related DT's. They are:-
2023-06-19 14:51:48.576 D/DBINFO_STAGE5: Entry is Entry4 it has 1 related DT's. They are:-
    	DT name = DT08 URL=dt08url ADDON=addonF REFERS TO PARENT ENTRY Entry4
2023-06-19 14:51:48.580 D/DBINFO_DT_STAGE5: DT is DT04 URL is dt04url ADDON is addonA REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.580 D/DBINFO_DT_STAGE5: DT is DT05 URL is dt05url ADDON is addon1 REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.580 D/DBINFO_DT_STAGE5: DT is DT06 URL is dt06url ADDON is addonD REFERS TO PARENT ENTRY Entry2
2023-06-19 14:51:48.580 D/DBINFO_DT_STAGE5: DT is DT08 URL is dt08url ADDON is addonF REFERS TO PARENT ENTRY Entry4

See the definition of DAO object - https://developer.android.com/training/data-storage/room/accessing-data#anatomy

You can write something like the following in your DAO:

@Dao
interface DataTableDao {
    ...
    @Delete
    fun delete(dataTable: DataTable)

    @Query("DELETE FROM datatable WHERE url = :urlInput")
    fun conditionalDelete(urlInput: String)
}

You can use the delete function (https://developer.android.com/training/data-storage/room/accessing-data#convenience-delete) to remove specific entry from DataTable, or you can define your own query (https://developer.android.com/training/data-storage/room/accessing-data#query) to do something more complex.