Can we have IN filter?

[flowbehappy]

Hi! Great job for InfluxDB.

And I wonder If it can support IN filter in where clause, so far it seems no supported:

SELECT * FROM mydb WHERE service IN ('aa', 'bb');

{
    "error": "error parsing query: found IN, expected SELECT, DELETE, SHOW, CREATE, DROP, GRANT, REVOKE, ALTER at line 1, char 321"
}

version: bdd469d

[otoolep]

We don't have support for IN right now but you can use OR, for the query you provided:

SELECT * from mydb WHERE service = 'aa' OR service = 'bb'

[otoolep]

I too would like this, and it should be easy with some query re-writing of the AST. Let me see if I can get to it.

[beckettsean]

A potentially easier workaround than multiple OR statements would be to use a regex. I think it should be faster, too. E.g.

SELECT * from mydb WHERE service =~ /aa|bb/

That said, supporting IN would be great, especially for greater congruence with SQL.

[srikara]

+1

[ch33hau]

Hi Guys,

Is this feature request still valid? If it is valid, can I work on this task?

Thanks

[jsternberg]

Yes, we are still interested in this feature.

[ch33hau]

Thanks, I will start working on this during weekend.

[MariaCobretti]

I would love this feature

[innovationhub-asia]

+1

[xywendz]

this feature any update?

[maxp-edcast]

Looking forward to this. I would be fine using the WHERE .. OR or regex alternatives, but even won't work when the list of values passed to IN is very large (20k plus). Right now I don't see such a way to process this data without causing a "url too large" error.

[jsternberg]

So this issue isn't likely to be resolved very quickly, but I thought I should point out that we support using POST for queries and that you can put the query itself in the body. I think you can use application/x-www-form-urlencoded or you can use multipart/form-data. If you use the first, just urlencode the query. If you use the second, you can upload the query to a file with the query parameter q and then just insert the query into that.

Here's an example of doing it in Go with a GET/POST and application/x-www-form-urlencoded:

params := url.Values{}
params.Set("q", "your super long query")
body = strings.NewReader(params.Encode())

req, _ := http.NewRequest("GET", "http://localhost:8086/query", body)
req.Header.Set("Content-Type", "application/x-www-form-urlencoded")
http.DefaultClient.Do(req)

Here's a (more complicated) example where you upload a file using POST. I'm honestly not sure if, practically, they are any different from each other.

var q io.Reader // this got defined at some point and is a really large query, maybe an *os.File?
body := bytes.NewBuffer(nil)
writer := multipart.NewWriter(body)

// Retrieve the filename if we are reading from a file.
// The server doesn't actually use this information, but it's nice to include anyway.
filename := "<stdin>"
if f, ok := q.(*os.File); ok {
	filename = filepath.Base(f.Name())
}

// Create the form file and copy the contents of the io.Reader into it.
f, err := writer.CreateFormFile("q", filename)
if err != nil {
	return nil, err
}
io.Copy(f, q)
writer.Close()

req, _ := http.NewRequest("POST", "http://localhost:8086/query", body)
req.Header.Set("Content-Type", writer.FormDataContentType())
http.DefaultClient.Do(req)

I hope that helps.

Note that you can mix query parameters and the body parameters so you can put things like db=testdb in the query while putting the query itself in the body.

[YEMEAC]

@kalidasya "We have another usecase, the support for IN would make it possible to integrate Grafana's multiselect feature with influx" here for this reason! i'm trying to use "all" option with a variable in Grafana but doesn't work, only if you select all them but one by one

[brian15co]

Is this a feature that is still being developed?

[walterdolce]

I just come across this issue as well while I was trying to show data aggregated by host (so same use case as the second comment before mine above). I believe many will benefit from this capability.

I am surprised it's not already implemented..

[atthom]

It would be great to have this feature.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.

[douglasg14b]

Oh, good to see that influx is using this stalebot cancer.

Christ.

[Gp2mv3]

Is there something that we can do to have a IN clause or a workaround with regexes ?

[mjiderhamn]

I think it would be great if someone could at least settle the discussion on the performance impact of using regexp vs OR. above @beckettsean "thinks" regexp should be faster, while here @cxreg has found support for his assumption that regexp "probably doesn't make use of the index", his link however being dead.

Docs also state that

queries with regular expressions are not as performant as those without

which is kind of obvious, but doesn't give any substantial guidance in this case.

So what's going on behind the scenes? Is the set of tags matching the regexp first being looked up and then used as a condition (should be fast for fairly sized sets), or is the tags of each entry matched against the regexp (potentially slooow) - or something completely different?

[CaptainMack]

+1 - using IN would be great in transitioning from Postgres to Influx

[atthom]

@mjiderhamn this would be great but this issue beeing closed, I don't think there will be an update on this topic anytime soon.
I guess most of the effort of influxdata is devoted to influxdb 2.0. Unfortunately for us, there is no reason for them to change the syntax of a previous influxdb version.

[ishita07-danaher]

Hi..I was trying to implement something similar using both reg ex and 'OR' .
Where these approaches fail :

Reg ex : if the value I have passed is contained in the column value, it reads that though it shouldn't. Reg ex is similar to 'contains'..I want to compare the exact same value.

OR : I want data for both values not either of them.

Please suggest another approach for IN clause.

[jeanflorentine]

I think the all point of having an IN() clause is not write
select * from myDB where service in ('aa', 'bb');
which can be achieved (painfully) with OR
but to use sub query such as
select * from myDB where service in (select distinct(k8S_container_name) from K8S_E4));

This is real purpose of in() to deal with temporary on the fly intermediary collections, ain't it ?

[GerZah]

Reg ex is similar to 'contains'..I want to compare the exact same value.

If RegEx specifies an exact term, it is equivalent to checking for an exact value – as opposed to "contains" or "is similar to", which one would usually associate with RegEx.

---

Please look at this WHERE service = 'aa' OR service = 'bb' vs. this WHERE service =~ /aa|bb/

The latter is a RegEx that specifies two exact values, aa and bb, with the | meaning "alternative", or in other words OR. … This could also be WHERE service =~ /aa/ OR service =~ /bb/

However, the beauty in the | RegEx notation is to specify the multiple exact values in one (potentially long) =~ term, but without all the mucking around with OR.

---

… Still an IN(…) clause would be really awesome – +1 from me for that.