Knex.js migrate data from one column to others conditionally

Question

I'm trying to do a migration with knex.js in a dirty MySQL database. So there is one column in which there is JSON with three different types of data. I already made a migration in which I created the three different columns for these different types of data.

What I now want to do, is conditionally move the the data from the column with the JSON to the three different columns so: if type == A: move to new_col_A else if type == B: move to new_col_B else: move to new_col_C So eventually this column with the JSON should be empty and the three new columns should be populated with the data that was in this JSON column.

Answer 1

Something like this should work (https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#operator_json-inline-path):

update `MyTable` set `new_col_A` = `old_col` where `old_col`->>'$.type' = 'A';
update `MyTable` set `new_col_B` = `old_col` where `old_col`->>'$.type' = 'B';
update `MyTable` set `new_col_C` = `old_col` where `old_col`->>'$.type' not in ('A', 'B');

-- now check that data was migrated correctly to new columns before setting old column to null
update `MyTable` set `old_col` = NULL;

I didn't try to run the code, so it might have some errors, but basic principle should work (I've been using json only with postgresql).

With knex you need to use raw queries to get this working, since it doesn't have any special support for json operations.