Extracting multiple values from JSON arrays

Comments

6 comments

  • Avatar
    Kevin Keech

    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
  • Avatar
    Brian Yu

    That worked, thanks!

    0
    Comment actions Permalink
  • Avatar
    Brian Yu

    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
  • Avatar
    Kevin Keech

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

    0
    Comment actions Permalink
  • Avatar
    Brian Yu

    0
    Comment actions Permalink
  • Avatar
    Kevin Keech

    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.