I am trying to separate this into each source and target strings. So end up with

Source USD
TOAUD - value
TOCAD - value
TODKK - value

{"USDAUD":"1.477443","USDCAD":"1.328465","USDDKK":"7.1803","USDEUR":"0.965285","USDGBP":"0.84274","USDJPY":"139.148502","USDNOK":"9.99368","USDNZD":"1.621725","USDSEK":"10.46051","USDSGD":"1.370005","USDTRY":"18.607298","USDZAR":"17.301898"}

{"NOKAUD":"1.477443","NOKCAD":"1.328465","NOKDKK":"7.1803","NOKEUR":"0.965285","NOKGBP":"0.84274","NOKJPY":"139.148502","NOKUSD":"9.99368","NOKNZD":"1.621725","NOKSEK":"10.46051","NOKSGD":"1.370005","NOKTRY":"18.607298","NOKZAR":"17.301898"}

This data comes from a rest api. I have put it into a data flow but seeing as the order can change due to the source currency changing and being switched out.

I was planning to use this in expression builder.

regexMatch(toString(byPath('quotes')),'"...AUD: "') - To test and with this I get a positive reply that it has found a match
regexExtract(toString(byPath('quotes')),'"...AUD:
"',1) - when I use this which I understood was the same just with extraction based on the group value the output is blank.

Could someone please explain where I am going wrong or if there is a better way.

Thank you

Hello @Oliver Adams ,
Thanks for the question and using MS Q&A platform.

I am a little confused on the end goal, but I do have some corrections for your REGEX.

Neither your match nor extract worked for me. There is typo expression.

compare  
"...AUD:"  
"USDAUD":"1.4  
Quote is after colon :  
Should be regexMatch(toString(byPath('quotes')),'"...AUD":')  

After getting the match to work, I played around with the extract. I found it is 0-based index, not 1-based index. At least when there are no capture groups?

regexExtract(toString(byPath('quotes')),'"...AUD":',0)  
returns   
"USDAUD":  
"NOKAUD":  

Since you want the numbers after that, we need to do something different.

regexExtract(toString(byPath('quotes')),'"(...AUD)":(\\d+\\.\\d+)',2)  

Here we have "capture groups" demarkated by (parenthesis). Now the index seems to be 1-based, not 0-based. The number references the capture groups. The second capture group (\\d+\\.\\d+) means "at least one digit, followed by decimal point, followed by at least one digit."

The discrepancy with whether the index is 0 based or 1 based seems strange to me. I'll ask whether this is bug or not.

There may be another way to go about your end goal, using rule-based mapping to merge all the column pairs. However I hope I have addressed your immediate issue.

Please do let me if you have any queries.

Thanks
Martin

  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
  • Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
  • @Oliver Adams I did some more testing, and think I know what is going on.

    regexExtract('foo bar baz', 'b..', 0)  -> bar  
    regexExtract('foo bar baz', 'b..', 1)  -> null  
    regexExtract('foo bar baz', '(b..)', 0)  -> bar  
    regexExtract('foo bar baz', '(b..)', 1)  -> bar  
    regexExtract('foo bar baz', '(b..) (b..)', 0)  -> bar baz  
    regexExtract('foo bar baz', '(b..) (b..)', 1)  -> bar  
    regexExtract('foo bar baz', '(b..) (b..)', 2)  -> baz  
    

    In the first pair, we have no capture groups.
    In the second pair, we have a single capture group.
    In the last trio, we have 2 capture groups.

    Notice how index 0 always returns results, and in the last one, returns ALL matches.
    Also index 1 only works when there is at least one capture group.
    This tells me index 0 is a raw collections of match(es). Positive numbers refer to capture groups.

    Since the original expression did not have a capture group but DID have a match, we only get results at index 0.

    I have sent an inquiry to the product group on this.