sql - Filter records using JSON function for JSON array

Question

There is one table where data stored in JSON format. I need to find how many records are there where Quote Required.

JSON

[{"id":14,"desc":"Job is incomplete.","quote_required":"Yes"}, 
 {"id":14,"desc":"appointment need to rebook","quote_required":"Yes","start-date":"2021-11-20"}]

I am trying to achieve about using below JSON_CONTAINS() and JSON_EXTRACT()

SELECT COUNT(*)   
  FROM `products`   
 WHERE JSON_CONTAINS( JSON_EXTRACT(submit_report, "$.quote_required"), '"Yes"' )

But I am getting 0 results here

Answer 1

You can search for each element of the array whether having quote_required equals to Yes through use of index values starting from 0 upto length of the array minus 1 by generating index values with recursive common table expression such as

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`

Demo