I was wondering what the best way is to compose a WHERE clause that matches multiple values for a tag. I was in the impression that i could solve this using a regex pattern but i seem to hit a wall. There is too many data returned in my query…
In my case i have several measurements that have an ‘location_id’ tag.
When i create a query using a where clause like below i get data back that is not correct. Probably to my misunderstanding on how to use the regex pattern or maybe it is impossible…
My data is as follows
time cpu location_id
---- ---- -----------
2017-11-27T07:00:00Z 159 2
2017-11-27T15:00:00Z 154 27
2017-11-27T23:00:00Z 117 7
2017-11-28T07:00:00Z 160 7
2017-11-28T15:00:00Z 167 27
2017-11-28T23:00:00Z 170 27
When i execute a query i only want the locations back with the value of ‘7’. But when i use a query like below the data from location_id 27 is also returned…
SELECT * FROM “measurement” WHERE location_id =~ /7/;
My goal is that i would like to indicate that the location_id should be in a list of values. Is this even possible with regex? Or should i use AND clauses?
SELECT * FROM “measurement” WHERE location_id =~ /7|2|104|45/;
This is possible with regex (albeit only with tags/fields that are strings). First, recall that the regex /7/ matches the character 7 anywhere in the input text. Therefore both "7" and "27" match.
To constrain the match to cover the entire input text, wrap it in start-of-text ^ and end-of-text $ markers. For example, the regex /^7$/ will match only the string "7" and nothing else.
To match against multiple entire strings, use the regex or operator |. Remember however that it has a lower operator precedence than composition, meaning we have to wrap the subexpression in parentheses. For example, /^(7|2|104|45)$/ will match against either "7", "2", "104", or "45".
See the golang regex syntax documentation for more details.