Create indexing on json field

ALTER TABLE missions ADD COLUMN start_date VARCHAR(256)
GENERATED ALWAYS AS ( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.start_date'))) VIRTUAL;

ALTER TABLE missions ADD COLUMN end_date VARCHAR(256)
GENERATED ALWAYS AS ( JSON_UNQUOTE(JSON_EXTRACT(`mission_data`, '$.end_date'))) VIRTUAL;

CREATE INDEX idx_tbl_missions_start_date ON missions(start_date);
CREATE INDEX idx_tbl_missions_end_date ON missions(end_date);

insert into missions(customer_id, mission_ref_no, mission_data, order_no) values(
    'abdullahalawal177@gmail.com',
    'Mission Ref1903- 21032019',
    '{"tva": 35.58, "sipsi": {"is_selected": true}, "duration": 2, "end_date": "2019-04-15", "carte_btp": {"is_selected": false}, "ref_number": "Mission Ref1903", "start_date": "2019-03-19", "unit_price": 60, "car_sticker": {"is_selected": false}, "total_price": 213.48, "package_type": "confort", "representation": {"end_date": "2019-04-15", "start_date": "2019-03-19", "is_selected": true, "number_of_month": 2, "type_of_secondment": "Usual", "number_of_employees": 1}, "arrival_country": "FR", "sub_total_price": 177.9, "renew_ref_number": null, "departure_country": "AF", "number_of_workers": 1, "sector_of_activity": "Transport", "type_of_secondment": "Usual", "informative_document": {"languages": [], "is_selected": false}, "is_permission_granted_to_ps": false}',
    '1552972234624'  
)

select * from missions where mission_ref_no = 'Mission Ref1903- 21032019'
select * from missions where start_date = '2019-03-19'


delete from missions where mission_ref_no = 'Mission Ref1903- 21032019'

ALTER TABLE missions DROP COLUMN start_date

ALTER TABLE missions DROP COLUMN end_date


//for notifications

ALTER TABLE notifications ADD COLUMN notification_created_for_status VARCHAR(256)
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(`attribute`, '$.notification_created_for_status'))) VIRTUAL;


CREATE INDEX idx_notifications_notification_created_for_status ON notifications(notification_created_for_status);

ALTER TABLE notifications
DROP COLUMN notification_created_for_status


Ref:

1.  https://www.dbrnd.com/2016/10/mysql-5-7-how-to-create-an-index-on-json-data-type-column-generated-virtual-column/
2. https://www.compose.com/articles/mysql-for-json-generated-columns-and-indexing/