How to extract a Room list column into a new linked table, migrating data https://blog.jakelee.co.uk/how-to-extract-a-room-list-column-into-a-new-linked-table-migrating-data/

Car.kt

class Car(
    @Embedded
    val metadata: CarMetadata,

    @Relation(
        parentColumn = "id", // The name of the CarMetadata ID field
        entityColumn = "carId" // The name of the Component's car ID field
    )
    var components: List<Component>
)

CarDatabase.kt

@Database(
    entities = [CarMetadata::class, Component::class],
    version = 2
)
internal abstract class CarDatabase: RoomDatabase() {
  
      companion object {
          @Volatile
          private var INSTANCE: CarDatabase? = null

          fun getDatabase(context: Context): CarDatabase {
              val tempInstance = INSTANCE
              if (tempInstance != null) {
                  return tempInstance
              }
              synchronized(this) {
                  val instance = Room
                      .databaseBuilder(context.applicationContext, CarDatabase::class.java, "car_database")
                      .addMigrations(MIGRATION_1_2)
                      .build()
                  INSTANCE = instance
                  return instance
              }
          }

          private val MIGRATION_1_2 = object : Migration(1, 2) {
              override fun migrate(database: SupportSQLiteDatabase) {
                  try {
                      // Extract existing components
                      val cursor = database.query("SELECT `id`, `components` FROM `Cars`")
                      val components = cursorToComponents(cursor)

                      // Make new table without components column, SQLite cannot delete columns
                      database.execSQL("CREATE TABLE IF NOT EXISTS `_new_Cars` ...")
                      database.execSQL("INSERT INTO `_new_Cars` ...")
                      database.execSQL("DROP TABLE `Cars`")
                      database.execSQL("ALTER TABLE `_new_Cars` RENAME TO `Cars`")

                      // Add new table & index
                      database.execSQL("CREATE TABLE IF NOT EXISTS `component` ...")
                      database.execSQL("CREATE INDEX IF NOT EXISTS `index_component_carId` ...")

                      // Insert new components
                      insertComponents(database, components)
                  } catch (e: Exception) {
                      // Migration failed, Room will automatically roll back the transaction and retry when DB accessed
                  }
              }
           }

          private fun cursorToComponents(cursor: Cursor): List<Component> {
              // 1: First get the creation IDs and serialized component lists until there are no more rows
              val carData = arrayListOf<Pair<String, String>>()
              if (cursor.moveToFirst()) {
                  val carIdIndex = cursor.getColumnIndex("carId")
                  val componentsIndex = cursor.getColumnIndex("components")
                  do {
                      carData.add(Pair(
                          cursor.getString(carIdIndex),
                          cursor.getString(componentsIndex)
                      ))
                  } while (cursor.moveToNext())
              }

              // 2: Parse each serialised list of components into objects, and update their carId
              val componentsList = carData.flatMap { carIdAndComponents ->
                  ComponentTypeConverter.toComponents(carAndComponents.second).onEach {
                      // 3: Update all carIds
                      it.creationId = carIdAndComponents.first
                  }
              }

              // 4: Return the list of components
              return componentsList
          }

           private fun insertComponents(database: SupportSQLiteDatabase, components: List<Component>) {
              val insertSql = "INSERT INTO " +
                      "Component(carId, componentId, type, description, created) " +
                      "VALUES (?, ?, ?, ?, ?)"
              val insertStatement = database.compileStatement(insertSql)
              components.forEach { 
                  insertStatement.clearBindings()
                  insertStatement.bindString(1, it.carId)
                  insertStatement.bindString(2, it.componentId)
                  insertStatement.bindString(3, it.type)
                  insertStatement.bindString(4, it.description)
                  insertStatement.bindString(5, it.created)
                  insertStatement.executeInsert()
           }
      }
}

CarMetadata.kt

@Entity(tableName = "Cars")
data class CarMetadata(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String,
    
    @ColumnInfo(name = "colour")
    val colour: String
)

Component.kt

@Entity(
    tableName = "Component",
    foreignKeys = [ForeignKey(
        entity = CarMetadata::class,
        parentColumns = arrayOf("id"), // The name of the CarMetadata ID field
        childColumns = arrayOf("carId"), // The name of the Component's car ID field
        onDelete = ForeignKey.CASCADE
    )]
)
data class Component(
    @ColumnInfo(name = "carId", index = true)
    val carId: String,
  
    @PrimaryKey
    @ColumnInfo(name = "componentId")
    val componentId: String,
    
    @ColumnInfo(name = "type")
    val type: String,
    
    @ColumnInfo(name = "description")
    val description: String,
    
    @ColumnInfo(name = "created")
    val created: Long
)

ComponentTypeConverter.kt

internal class ComponentTypeConverter {

    companion object {

        @TypeConverter
        @JvmStatic
        fun fromComponents(components: List<Component>): String {
            return ComponentTypeConverterGson.create().toJson(components)
        }

        @TypeConverter
        @JvmStatic
        fun toComponents(jsonComponents: String): List<Component> {
            val componentsType = object : TypeToken<List<Component>>() {}.type
            return Gson().fromJson<List<Component>>(jsonComponents, componentsType)
        }
    }
}