MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Creating a table with mentioned fields
CREATE TABLE transactions (`transaction_details` JSON, 
                           `transaction_type` INT,
                           `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP);
-- Inserting shown data
INSERT INTO `transactions` (`transaction_details`, `transaction_type`) VALUES
('{"message":"fgdfgdfg","referral_compensation_date":"2019-11-30","compensation_status":1,"compensation_type":2,"allocated_count":"-NA-","daily_job_count":"-NA-","allocation_date":"2019-11-30"}', 7);
-- Check the data
SELECT * FROM `transactions`;


transaction_details
transaction_type
updated_at


7b226d657373616765223a20226667646667646667222c2022616c6c6f63617465645f636f756e74223a20222d4e412d222c2022616c6c6f636174696f6e5f64617465223a2022323031392d31312d3330222c20226461696c795f6a6f625f636f756e74223a20222d4e412d222c2022636f6d70656e736174696f6e5f74797065223a20322c2022636f6d70656e736174696f6e5f737461747573223a20312c2022726566657272616c5f636f6d70656e736174696f6e5f64617465223a2022323031392d31312d3330227d
7
2022-03-29 08:01:43


-- Check used expressions
SELECT json_unquote(json_extract(`transaction_details`, '$."compensation_status"')) exp1,
       json_unquote(json_extract(`transaction_details`, '$."compensation_type"')) exp2
FROM `transactions` ;


exp1
exp2


1
2


-- Check OP's query
select * 
from `transactions` 
where (json_unquote(json_extract(`transaction_details`, '$."compensation_status"')) = 1 
  and `transaction_type` = 7 
  and json_unquote(json_extract(`transaction_details`, '$."compensation_type"')) = 2) 
order by `updated_at` asc;


transaction_details
transaction_type
updated_at


7b226d657373616765223a20226667646667646667222c2022616c6c6f63617465645f636f756e74223a20222d4e412d222c2022616c6c6f636174696f6e5f64617465223a2022323031392d31312d3330222c20226461696c795f6a6f625f636f756e74223a20222d4e412d222c2022636f6d70656e736174696f6e5f74797065223a20322c2022636f6d70656e736174696f6e5f737461747573223a20312c2022726566657272616c5f636f6d70656e736174696f6e5f64617465223a2022323031392d31312d3330227d
7
2022-03-29 08:01:43