MariaDB 10.5

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

CREATE TABLE `products`( submit_report JSON );
INSERT INTO `products` VALUES
('[{"id":14,"desc":"Job is incomplete.","quote_required":"Yes"},
   {"id":14,"desc":"appointment need to rebook","quote_required":"Yes","start-date":"2021-11-20"}]');
                              
WITH recursive cte AS 
(
  SELECT 0 AS n
  UNION ALL
  SELECT n + 1 AS value
    FROM cte
   WHERE cte.n < ( SELECT JSON_LENGTH(submit_report) - 1 FROM `products` ) 
)
SELECT SUM(JSON_CONTAINS(JSON_EXTRACT(submit_report, CONCAT("$[",n,"].quote_required")),
                                      '"Yes"')) AS count
  FROM cte
  JOIN `products`


count


2