Combine / Join SQL queries

I have a query which gets data from a JSON document, checks which values have been returned and then returns that count.

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
     )

As part of this query, I’d like to also return cat_name, dog_name and add a WHERE pet_store = 'london'

I’m not sure exactly where I’m suppose to add the other parameters or if I should join the subquery?

Answer

SELECT cat_name, dog_name,
     (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
      WHERE pet_store = 'london'
     )