[feature request] make regex work on field keys and values

[kurtharriger]

The following query returns results:
select * from "multiHistReport" where time > now() - 1h and eventDescription='component ready' and userAgent= 'Chrome' and pageHash= '_iterationstatus' order by asc

but this query does not:
select * from "multiHistReport" where time > now() - 1h and eventDescription='component ready' and userAgent=~ /Chrome/ and pageHash= '_iterationstatus' order by asc

The difference is the regex for userAgent.

I have found influx 0.9 to require a space after ~ and other regex expressions work fine so not sure why this one does not.

[kurtharriger]

It occurred to me what is different in this case.
I ported a samza job targeting influx 0.8 to influx 0.9. In influx 0.8 these were fields, when I ported it to influx 0.9 I did not change this to be tags.

So the difference it would seem is that regex only works on tags not fields.

[kurtharriger]

I moved the field values into tags and confirmed I was then able to use a regex.
However, I did need to drop and recreate the database for this to work. It appears if a field with the column name exists the regex does not work even if tag column is also present.

[pauldix]

What if you make the regex /.*Chrome.*/?

[kurtharriger]

I had tried that too without success before moving the columns to tags.

[beckettsean]

Issue #2615 already opened to prevent duplicate tag and field keys. #2714 will fix the =~ 'literal' bug.

[AshtonDavis]

What's the status on this? We'd really appreciate this functionality.

[thunderstumpges]

+1 this is needed incredibly badly now that all of telegraf is reporting measurements in the fields. Dashboard building just got ridiculously tedious.

I also wanted to point out that this needs to support regex not only in the WHERE clause, but in the SELECT clause e.g.:

SELECT mean("/cpu/") FROM ...

This avoids having to specify every single cpu value separately.

[jsternberg]

@thunderstumpges I think your request is different from the one expressed in this issue. Can you make a new issue for that one describing what you want to be able to do and the expected output when you run the above command and mention me on it?

Thanks.

[thunderstumpges]

How is this different? The issue says "Make regex work on field keys and values". I only said it should work in the select clause and the where clause. If you think this needs to be a separate issue, I'd gladly create it and reference this one.

[thunderstumpges]

@jsternberg this is done, see #5955

[jsternberg]

Thanks @thunderstumpges. Do you also want the ability to do something like this?

SELECT value FROM cpu WHERE /cpu_.*/ > 10

Where you have a bunch of field keys such as cpu_usage and you want all or one of them to be true? If you do, please make a separate issue for that too. I'm just going to fix the ability to use a regex on field values for this issue since that seems to be what it was originally about.

[thunderstumpges]

Thanks, I am mostly needing to not need to specify individual fields in the select clause. I don't have an immediate use case for that last scenario.

[zezuladp]

I have a use case for something like such:

SELECT value FROM cpu WHERE /cpu_.*/ > 10

Basically when importing a list into influxdb I "flatten" into item.0, item.1, etc. I want to check if any of those have a specific value.
@jsternberg Let me know if you want/need any more information. Is this something that's doable?

[vtcsiva]

SELECT value FROM cpu WHERE /cpu_.*/ > 10 is this supported in V1.3 ? getting syntax error right now in V1.3 ,if its is plese provide the regx Examples. Peroformance impact if this is running on Cluster node.