Well, I’ve been working on this, and finally get to a solution, the main problem is that I was trying to sum and count the different situations of my picking_data.found, so the answer to this was applying the JSON_SEARCH function which allows me to search for a string inside the JSON, but the problem was that the results were in boolean, so I did a little transformation and tah dah. Got this, and the problem is resolve. Thx @gillepa for your response, it took me to think of another path to this problem.
select
JSON_LENGTH(JSON_SEARCH(replace(replace(
replace(products_list,
'"found":true',
'"found": "PRODUCT_FOUND"'),
'"found":false',
'"found": "PRODUCT_NOT_FOUND"'),
'"found":null',
'"found": "PRODUCT_UNKNOWN_STATUS"'), 'all', 'PRODUCT_FOUND', NULL, '$.products[*].picking_data.found')) as found,
JSON_LENGTH(JSON_SEARCH(replace(replace(
replace(products_list,
'"found":true',
'"found": "PRODUCT_FOUND"'),
'"found":false',
'"found": "PRODUCT_NOT_FOUND"'),
'"found":null',
'"found": "PRODUCT_UNKNOWN_STATUS"'), 'all', 'PRODUCT_NOT_FOUND', NULL, '$.products[*].picking_data.found')) as not_found,
JSON_LENGTH(JSON_SEARCH(replace(replace(
replace(products_list,
'"found":true',
'"found": "PRODUCT_FOUND"'),
'"found":false',
'"found": "PRODUCT_NOT_FOUND"'),
'"found":null',
'"found": "PRODUCT_UNKNOWN_STATUS"'), 'all', 'PRODUCT_UNKNOWN_STATUS', NULL, '$.products[*].picking_data.found')) as unknown
from order_tickets
It is a little ugly, but Now i’m transforming it to a function to beautify the results lol.
Thanks for the help <3