Extracting multiple values from JSON arrays

Follow

Brian Yu

• October 29, 2019 21:13
• Edited

Hi, I have a series of test executions compiled into a single JSON message every hour.  I'm trying to extract the execution status of each test with the test's associated identifiers.  My current query looks like this:

_sourceCategory="xxxx" and _collector="xxxx"
| json "executions[*].cycleId" as cycleId
| json "executions[*].versionId" as versionId
| json "executions[*].status.id" as status
| extract field=cycleId"(?<cycle>\d\d\d\d\d\d)" multi
| extract field=versionId"(?<version>\d\d\d\d\d)" multi

This should only generate 20 messages, as there are only 20 executions.  However, it generates 400 messages instead.  Is there a reason why it associates 20 versionIds for each cycleId?  How can I generate only 20 messages, each populated with the corresponding cycleId and versionId?  Can I also add the corresponding status to each of the 20 messages as well?  Thanks!

0

• 

  Kevin Keech

  • October 29, 2019 21:41

  Hi Brian,

  I think the issue here is you provide two separate multi parsing operations.  The first parses 20 values for the cycleID, then the second parses an additional 20 values for each of the 20 cycle IDs generated by the first parsing operation, thus giving you 400 messages in the end.
  
  What you can try to do is combine these two parsing operations into one using a comma-separated list.  Note you'll need to use the "parse regex" operation in place of extract and the secondary parses just reference "regex". 
  
  

  _sourceCategory="xxxx" and _collector="xxxx"
  | json "executions[*].cycleId" as cycleId
  | json "executions[*].versionId" as versionId
  | json "executions[*].status.id" as status
  | parse regex field=cycleId"(?<cycle>\d\d\d\d\d\d)", regex field=versionId"(?<version>\d\d\d\d\d)" multi
   

  Hope this works for you. 

  1

  Comment actions Permalink
• 

  Brian Yu

  • October 29, 2019 21:57

  That worked, thanks!

  0

  Comment actions Permalink
• 

  Brian Yu

  • October 30, 2019 19:01

  Hi Kevin Keech,

  It seems I celebrated too soon.  While your solution did reduce the amount of duplication I was getting, I still dont have my desired results.  I am now getting all values of the version Ids extract for a single cycle Id: i.e. I have 20 cycle Ids and 20 version Ids, and now I have all 20 version Ids matched to 1 cycle Id.  Do you have another solution?

  Thanks.

  0

  Comment actions Permalink
• 

  Kevin Keech

  • October 30, 2019 23:30

  Could you supply an example message you are attempting to parse so we can get the full picture?

  0

  Comment actions Permalink
• 

  Brian Yu

  • October 31, 2019 15:16

  

  0

  Comment actions Permalink
• 

  Kevin Keech

  • October 31, 2019 21:37

  Hi Brian,
  
  Thanks for the example. (PS I saw your related case but going to answer here to try and help the broader audience)

  So this is actually going to require a slightly different set of parsing operations. The problem with the existing query is that once the objects and versions are parsed into separate arrays using the JSON operation there is no way to link back the individual object/version/status with subsequent parsing. This is a limitation of this operator. 
  
  Here is the query I would use in place of what you had originally, which uses just a single parse regex operation. I gave this a try with your sample message and it appears to parse each "status ID", versionID, cycleID into 5 lines. 

  _sourceCategory="xxxx" and _collector="xxxx"
  | parse regex "\"status\".*?\"id\": (?<status_id>\d+).*?\"versionId\": (?<version>\d+).*?cycleId\": (?<cycleid>\d+?)," multi

  0

  Comment actions Permalink

Please sign in to leave a comment.