How to extract multiple values from a single line result in a template variable

[bujupah]

Platform: Linux | Windows
Version: Grafana 9.1.2

What am doing: I am querying a datasource using variable feature to pull some data to use for filtering.

Problem statement: The thing is the result from query I am getting is a single line string with values separated by a comma.
eg: INC001,INC002,INC003,INC004…

The twist here is, I want the result as an array of values such as “INC001”,“INC002”,“INC003”. but my datasource does not have any feature to split this string into an array.

And so I tried to use the regex feature to write a regex group to be able to extract only the words from the string, but sadly I couldn’t…

Current result:

What I want:

This is what I am trying to do:

Using regex group to extract all the values from that line.

[grant2]

I am sure some Regex Ninja will come by here and post a solution, but maybe this or this will help in the meantime?

[bujupah]

Nope, no chance…
It seems like that regex option is only extracting a value per row… which in my case I only have one single row

[yosiasz]

Select keep union
Select it union
Select simple 

[bujupah]

SQL ERROR: syntax error at or near simple

Trying to hack it up, probably I will raise a PR soon to enhance that regex

[yosiasz]

Put quotes on values

Select 'keep' union
Select 'it' union
Select 'simple'

[yosiasz]

or another way

my view on regex, yes use it on rare occasions when you have no control on how the date comes to you. Imho I do not recommend it to be used willy nilly. and Definitely not to hack it up

[bujupah]

no but my datasource is returning comma separated values and I want to split thru all and get a final result

[yosiasz]

isn’t this your data source?

[bujupah]

Yes, but I am getting that same result as a response…
That query is just a mock

[yosiasz]

gotcha will the comma delimited data fluctuate from 3 values to 10 etc?

select unnest(string_to_array(incidents, ',')) as incidents
  from (select 'INC001,INC002,INC003,INC004' as incidents) a

no regex, no hacks, no PR

[bujupah]

here comes the problem… have limited access to functions in db
and all split function are disabled…

[yosiasz]

so what is the datasource providing you this data? a rest api?

[bujupah]

yes
also a csv datasource
and a postgresql - with limited access of functions

[achuthadivine]

I am stuck at the same problem. Were you able to solve this ?

[bujupah]

I was able to do it somehow using json api…
I can help if needed, can you share what kind of datasource you are using?