How can I compare an array column with JSON Object column and return the maximum discount in PHP MYSQL

Question

As requested by @Adyson, I've added my database sample below.

CREATE TABLE test.patients ( id BIGINT NOT NULL AUTO_INCREMENT , branchID INT NOT NULL , patientNo VARCHAR(20) NOT NULL , billingAccounts VARCHAR(50) NOT NULL , firstName VARCHAR(20) NOT NULL , lastName VARCHAR(20) NOT NULL , PRIMARY KEY (id)) ENGINE = InnoDB;

INSERT INTO `patients` (`id`, `branchID`, `patientNo`, `billingAccounts`, `firstName`, `lastName`) VALUES (NULL, '1', 'PN017830', '[\"-1\",\"-2\",\"7632\",\"7774\"]', 'John', 'Daka'), (NULL, '1', 'PN017890', '[\"-1\",\"-2\",\"8120\",\"7742\"]', 'Ann', 'Mikail')


CREATE TABLE `test`.`products` ( `id` INT NOT NULL AUTO_INCREMENT , `type` ENUM('pharmaceutical','other') NOT NULL , `productCode` VARCHAR(50) NOT NULL , `brand` VARCHAR(50) NOT NULL , `manufacturer` INT NOT NULL , `generics` TEXT NOT NULL , `privateBranchID` INT NOT NULL , `regID` INT NOT NULL , `regDate` DATETIME NOT NULL , `status` ENUM('active','deactivated') NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

INSERT INTO `products` (`id`, `type`, `productCode`, `brand`, `manufacturer`, `generics`, `privateBranchID`, `regBy`, `regDate`, `status`) VALUES (10, 'pharmaceutical', '500015806877', 'gaviscon', '217', '[\"magaldrate\",\"simethicone\"]', '0', '1', CURRENT_TIMESTAMP, 'active'), (11, 'pharmaceutical', '7640153080325', 'lofral', '199', '[\"amlodipine\"]', '0', '1', CURRENT_TIMESTAMP, 'active')

CREATE TABLE `test`.productdiscounts ( `id` BIGINT NOT NULL AUTO_INCREMENT , `branchID` INT NOT NULL , `productID` BIGINT NOT NULL , `accountID` BIGINT NOT NULL , `discount` DECIMAL NOT NULL , `isActive` ENUM('1','0') NOT NULL , `regBy` INT NOT NULL , `regTimestamp` DATETIME on update CURRENT_TIMESTAMP NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

INSERT INTO `productdiscounts` (`branchID`, `productID`, `accountID`, `discount`, `isActive`, `regBy`, `regTimestamp`) VALUES ('1', '10', '7723', '90', '1', '1', '2022-08-25 08:01:59'), ('1', '10', '7724', '70', '1', '1', '2022-08-25 08:01:59'), ('1', '10', '-2', '70', '1', '1', '2022-08-25 08:01:59'), ('1', '10', '7720', '55', '1', '1', '2022-08-25 08:01:59')

CREATE TABLE chms.patients ( id BIGINT NOT NULL AUTO_INCREMENT , branchID INT NOT NULL , patientNo VARCHAR(20) NOT NULL , billingAccounts VARCHAR(50) NOT NULL , firstName VARCHAR(20) NOT NULL , lastName VARCHAR(20) NOT NULL , PRIMARY KEY (id)) ENGINE = InnoDB;

INSERT INTO patients (id, branchID, patientNo, billingAccounts, firstName, lastName) VALUES (NULL, '1', 'PN017830', '["-1","-2","7632","7774"]', 'John', 'Daka'), (NULL, '1', 'PN017890', '["-1","-2","8120","7742"]', 'Ann', 'Mikail')

CREATE TABLE test.products ( id INT NOT NULL AUTO_INCREMENT , type ENUM('pharmaceutical','other') NOT NULL , productCode VARCHAR(50) NOT NULL , brand VARCHAR(50) NOT NULL , manufacturer INT NOT NULL , generics TEXT NOT NULL , privateBranchID INT NOT NULL , regID INT NOT NULL , regDate DATETIME NOT NULL , status ENUM('active','deactivated') NOT NULL , PRIMARY KEY (id)) ENGINE = InnoDB;

INSERT INTO products (id, type, productCode, brand, manufacturer, generics, privateBranchID, regBy, regDate, status) VALUES (10, 'pharmaceutical', '500015806877', 'gaviscon', '217', '["magaldrate","simethicone"]', '0', '1', CURRENT_TIMESTAMP, 'active'), (11, 'pharmaceutical', '7640153080325', 'lofral', '199', '["amlodipine"]', '0', '1', CURRENT_TIMESTAMP, 'active')

CREATE TABLE test.productdiscounts ( id BIGINT NOT NULL AUTO_INCREMENT , branchID INT NOT NULL , productID BIGINT NOT NULL , accountID BIGINT NOT NULL , discount DECIMAL NOT NULL , isActive ENUM('1','0') NOT NULL , regBy INT NOT NULL , regTimestamp DATETIME on update CURRENT_TIMESTAMP NOT NULL , PRIMARY KEY (id)) ENGINE = InnoDB;

INSERT INTO productdiscounts (branchID, productID, accountID, discount, isActive, regBy, regTimestamp) VALUES ('1', '10', '7723', '90', '1', '1', '2022-08-25 08:01:59'), ('1', '10', '7724', '70', '1', '1', '2022-08-25 08:01:59'), ('1', '10', '-2', '70', '1', '1', '2022-08-25 08:01:59'), ('1', '10', '7720', '55', '1', '1', '2022-08-25 08:01:59')

This is my PHP code:

$searchSQL="
    select 
        distinct 
        products.id,
        products.type as productType,
        products.brand,
        products.status,
        products.productCode,
        products.generics,
        coalesce((select discount from productdiscounts where(productID=products.id and branchID=1 and isActive='1' and patients.billingAccounts like concat('%\"',productdiscounts.accountID,'\"%')) order by discount desc limit 1),0.00) as discount,
        concat('[',(select group_concat('{\"productID\":\"',productdiscounts.productID,'\",\"accountID\":\"',productdiscounts.accountID,'\",\"discount\":\"',productdiscounts.discount,'\",\"accountName\":\"',accounts.name,'\",\"accountNo\":\"',accounts.accountNo,'\"}') from productdiscounts 
        left join accounts on(productdiscounts.accountID=accounts.id)
        where(productdiscounts.productID=products.id and productdiscounts.branchID = 1 and productdiscounts.isActive = '1' )),']') as allDiscounts
    from products 
    left join stock on (products.id=stock.productID) 
    left join pricetags on (stock.priceTag=pricetags.id) 
    left join countries on (products.manufacturer=countries.id)
    left join diagnosis on (diagnosis.diagnosisRef='')
    left join patients on (patients.id=10)
    where(
        products.productCode='' or 
        products.generics like concat('%\"','magaldrate','%\"%') or  
        products.brand like concat('% ','gaviscon','%')
        and (stock.isActive='1' and products.status='active')
        )
    order by products.brand limit 30
";
$productsQ=(new DB)->getRef()->prepare($searchSQL); 
$productsQ->execute([]]);
$productsD=$productsQ->fetchAll();

I have a column (billingAccounts) that stores data as JSON Array like this: ["-1","-2","7632","7774"] and in the product-discount table, I have rows for each company's discount and to get the discount, I use the below code but is a bit slow: [enter image description here][1] select products.brand, coalesce((select discount from productdiscounts where(productID=products.id and branchID=? and isActive='1' and patients.billingAccounts like concat('%"',productdiscounts.accountID,'"%')) order by discount desc limit 1),0.00) as discount from products left join patients on (patients.patientNo=diagnosis.patientNo) .....

ALL I WANT IS TO RETURN THE MAXIMUM DISCOUNT FROM THE company ACCOUNTS THAT HAVE DISCOUNT ON THE PRODUCT AND ALSO MATCHES IN THE PATIENT BILLING ACCOUNS.

Note: I don't want to use concat or group_concat because is what I'm currently using and it's making the query to be slow!