How to use NRQL capture function to extract JSON data

Hi,

I try to follow this article to extra JSON data with NRQL capture function.

My data looks like this (return from the Query Builder):

"tags": "{\"ComputerName\":\"WIN-6EA8FD508DE.prd.aws.hello.com\",\"Name\":\"live global\"}"

I try both

SELECT capture(tags, r'\\"ComputerName\\":\\"(?P<name>.*?)\\"'), tags FROM MyTable
and
SELECT capture(tags, r'"ComputerName":"(?P<name>.*?)"'), tags FROM MyTable

Both queries return null for the name field.

I tested my Regex on https://regex101.com/ and everything works fine. It seems the tags field stored in NewRelic is different from showing in the UI.

How do I know the raw data I should use for the regex?

Cheers,

Tao

OK. I figured out by myself.

The NRQL capture function supports full Regex scan only. It means your Regex needs to match the full string instead of partial.

So my NRQL needs to be:
SELECT capture(tags, r'.*?"ComputerName":"(?P<name>.*?)".*?'), tags FROM MyTable

I hope this can help others.

2 Likes

Thanks @tao.lin1 - great work! Really appreciate you sharing here in case its useful to anyone else.

Hello,

I need to capture the used k/v pair tried a lot but struggling to get it:

SELECT capture(clintaApiKeysIds, r’(?P.*)’)

regex on this JSON string:

[{“dasda”:{“quota”:{“limit”:100000,“period”:“MONTH”},“usage”:{“used”:427,“remaining”:22003,“percentage”:0.00427}}}]

Hi all!
I’m struggling with a similar error. The thing that you asked for, I could solve it with “aparse” function instead of “capture”. Is much easier and faster than using Regex and you can use the “%” to represent “the rest of the string” to solve the problem that you were saying.

Now, I am having trouble doing this parse but with an array of JSON. I leave here the link to the question thread that I just open if anyone can help me with this.
https://discuss.newrelic.com/t/convert-a-message-attribute-of-a-log-that-is-a-array-of-json-to-a-table/191251
Thanks!