MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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}]}');
SELECT payment_no, JSON_PRETTY(cancel) FROM payment;


payment_no
JSON_PRETTY(cancel)


2024012150200010
{
  "cancels": [
    {
      "amount": 100,
      "cancel_no": "202401215050123"
    }
  ]
}


ALTER TABLE payment
  ADD COLUMN cancel_no VARCHAR(50)
    GENERATED ALWAYS AS (cancel->>'$.cancels[0].cancel_no')
    VIRTUAL INVISIBLE,
ADD INDEX idx_cancel_no (cancel_no);

Records: 0  Duplicates: 0  Warnings: 0


INSERT INTO payment (payment_no, cancel) VALUES
('2024012150200011', '{"cancels": [{"cancel_no": "202401215050122", "amount": 100}]}');
SELECT * FROM payment;
SELECT payment_no, JSON_PRETTY(cancel), cancel_no FROM payment;


payment_no
cancel


2024012150200010
7b2263616e63656c73223a205b7b22616d6f756e74223a203130302c202263616e63656c5f6e6f223a2022323032343031323135303530313233227d5d7d

2024012150200011
7b2263616e63656c73223a205b7b22616d6f756e74223a203130302c202263616e63656c5f6e6f223a2022323032343031323135303530313232227d5d7d




payment_no
JSON_PRETTY(cancel)
cancel_no


2024012150200010
{
  "cancels": [
    {
      "amount": 100,
      "cancel_no": "202401215050123"
    }
  ]
}
202401215050123

2024012150200011
{
  "cancels": [
    {
      "amount": 100,
      "cancel_no": "202401215050122"
    }
  ]
}
202401215050122


-- query  --
EXPLAIN 
  SELECT cancel 
  FROM payment
  WHERE cancel_no = '202401215050123';


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


1
SIMPLE
payment
null
ref
idx_cancel_no
idx_cancel_no
203
const
1
100.00
null

payment_no	cancel
2024012150200010	7b2263616e63656c73223a205b7b22616d6f756e74223a203130302c202263616e63656c5f6e6f223a2022323032343031323135303530313233227d5d7d
2024012150200011	7b2263616e63656c73223a205b7b22616d6f756e74223a203130302c202263616e63656c5f6e6f223a2022323032343031323135303530313232227d5d7d

payment_no	JSON_PRETTY(cancel)	cancel_no
2024012150200010	{ "cancels": [ { "amount": 100, "cancel_no": "202401215050123" } ] }	202401215050123
2024012150200011	{ "cancels": [ { "amount": 100, "cancel_no": "202401215050122" } ] }	202401215050122