SELECT with COUNT(*) to return an integer to a variable

Question

I am trying to run a SELECT to count some items into a table into GCP BigQuery to get the amount of items that have a "failed" status for a specific row.

I am using bq query for that and my command looks like this:

bq query \
--use_legacy_sql=false \
'
SELECT COUNT(*)
FROM
  `myproject.mydataset.mytable` CROSS JOIN UNNEST(controls) AS c
WHERE
  profile LIKE "%/myprofile/%"
AND
  vm = "myvm"
AND
  c.status = "failed"
'

I want to assign the results to a variable on a shell script, so I run this:

MYVAR=$(bq query --use_legacy_sql=false 'SELECT COUNT(*) FROM `myproject.mydataset.mytable` CROSS JOIN UNNEST(controls) AS c WHERE profile LIKE "%/myprofile/%" AND vm = "myvm" AND c.status = "failed"')

But when I echo $MYVAR I got a table like this:

echo $MYVAR
+-----+
| f0_ |
+-----+
|   6 |
+-----+

I wanted the result to be an integer, like:

echo $MYVAR
6

How can I make the SQL SELECT return just an integer? Or how can I filter the result table and get only the result field?

Thank you

Answer 1

The deafult output of the bq command is supposed to be human readable. If we look at the documentation for the bq command we see a flag called --format that allows us to control the output. There are two values that may be of use to us ... these are:

• json
• csv

One outputs as a JSON document and the other as CSV data. Since the output of a query is a table, you should realize that a scalar value is a special case.

My recommendation ... look at the output of running:

MYVAR=$(bq query --format=json --use_legacy_sql=false 'SELECT COUNT(*) FROM `myproject.mydataset.mytable` CROSS JOIN UNNEST(controls) AS c WHERE profile LIKE "%/myprofile/%" AND vm = "myvm" AND c.status = "failed"')

You will likely find that the output is now a JSON string. From there, you should be prepared to parse/extract from the JSON. It looks like you may be writing Liunx shell script. Consider looking at the jq command line tool for JSON processing.