I have an existing database with millions of rows which don't have a partition. I can alter it to add partition, however, the column that I wanted to add partition with contains JSON value and I need to extract that first.
ALTER TABLE api_backup_records
PARTITION BY RANGE(CAST(JSON_EXTRACT(custom_data, '$.customer_id') AS UNSIGNED))(
PARTITION cus10000 VALUES LESS THAN (10000),
PARTITION cus20000 VALUES LESS THAN (20000),
PARTITION cus30000 VALUES LESS THAN (30000),
PARTITION cus40000 VALUES LESS THAN (40000),
PARTITION cus50000 VALUES LESS THAN (50000),
PARTITION cus60000 VALUES LESS THAN (60000),
PARTITION cus70000 VALUES LESS THAN (70000),
PARTITION cus80000 VALUES LESS THAN (80000),
PARTITION cus90000 VALUES LESS THAN (90000),
PARTITION cusmax VALUES LESS THAN MAXVALUE)
I'm getting an error #1564 - This partition function is not allowed, of which I believe is because of the JSON_EXTRACT function which is not allowed in partitioning. I tried to alter the table by adding a new column with generated value based off of the JSON_EXTRACT, however that isn't supported on MongoDB.
I am wanting to easily search a Customer ID's (customer id being located on the JSON value of custom_data column [this is a custom data, so we need this to remain as a JSON since this will be a dynamic column]) record by partitioning it.
Thank you so much for helping.
