MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.36


create table payment
(
    payment_no varchar(50) not null
        primary key,
    cancel     json        null
);
INSERT INTO payment (payment_no, cancel) VALUES ('2024012150200010', '{"cancels": [{"cancel_no": "202401215050123", "amount": 100}, {"cancel_no": "202401215050124", "amount": 101}]}');
INSERT INTO payment (payment_no, cancel) VALUES ('2024012150200011', '{"cancels": [{"cancel_no": "202401215050124", "amount": 200}, {"cancel_no": "202401215050125", "amount": 201}]}');
INSERT INTO payment (payment_no, cancel) VALUES ('2024012150200012', '{"cancels": []}');
INSERT INTO payment (payment_no, cancel) VALUES ('2024012150200013', '{"cancels": [{"cancel_no": "202401215050126", "amount": 200}]}');
EXPLAIN SELECT cancel FROM payment WHERE JSON_CONTAINS(cancel->'$.cancels[*].cancel_no', '202401215050124');


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
payment
null
ALL
null
null
null
null
4
100.00
Using where


ALTER TABLE payment
  ADD INDEX cancel_no_idx ((
    CAST(cancel->>"$.cancels[*].cancel_no" as CHAR(255) ARRAY)
  )) USING BTREE;

Records: 0  Duplicates: 0  Warnings: 0


EXPLAIN SELECT cancel FROM payment WHERE JSON_CONTAINS(cancel->'$.cancels[*].cancel_no', '202401215050124');


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
payment
null
range
cancel_no_idx
cancel_no_idx
1023
null
2
100.00
Using where