Skip to content
Advertisement

Count number of values in SQL query / bigquery

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.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement