I have a query which extracts some data from a JSON document and I have a query that based on the number of values returned displays an overall column count. I can’t seem to work out how to combine these into a single query? assume that I need to use a sub-query but not sure where to go from here?
SELECT JSON_EXTRACT_SCALAR(data, '$.cat.name') as cat_name JSON_EXTRACT_SCALAR(data, '$.dog.name') as dog_name FROM table
SELECT CASE WHEN cat_name IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN dog_name IS NOT NULL THEN 1 ELSE 0 END AS cat_dog_total FROM table
Advertisement
Answer
You can use a subquery to maintain readability:
SELECT (CASE WHEN cat_name IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN dog_name IS NOT NULL THEN 1 ELSE 0 END ) AS cat_dog_total from (select JSON_EXTRACT_SCALAR(data, '$.cat.name') as cat_name JSON_EXTRACT_SCALAR(data, '$.dog.name') as dog_name from table ) t
Of course, you can substitute in the JSON_EXTRACT_SCALAR()
expressions as well, but this is more readable.