Bigquery Where = ANY-CodePudding

I am querying Bigquery using Grafana, In Grafana i use a text box variable(SrcAddr) to select certain values.

Select * from flow_tables where SrcAddr = '100.100.100.1'

if the User did not provide any input then all the IP addresses need to be selected. What value needs to be used to select all the IPs?

Select * from flow_tables where SrcAddr = 'ANY'

I tried using *, ANY, "." but it didnot help.

CodePudding user response：

Assuming that if the User did not provide any input it returns null - use something like below

Select * from flow_tables where SrcAddr = ifnull(<whatever user selected>, SrcAddr)

CodePudding user response：

One way would be to query for all addresses first and then use them to filter the actual results if no input was made into the text box. If there is an input, use that.

Example:

SELECT * 
FROM your_table
WHERE SrcAddr IN UNNEST(
  (
    SELECT CASE
    WHEN '${your_variable}' = '' THEN (SELECT ARRAY_AGG(SrcAddr) FROM your_table)
    ELSE (SELECT ['${your_variable}'])
    END
  )
)

(Assuming you named your variable your_variable and the column to filter by is SrcAddr and that column is a STRING column in the table your_table.)