Parsing JSON Array In Event

Elastic Stack Logstash
1

I am using the jdbc_streaming filter to pull additional data for an event from a database, the result looks like below. Any ideas on how I could have this parsed out so that I don't lose any of the data and keep it all contained within a single event?

[
  {
    "integeranswer": null,
    "dropdownid": 47,
    "questionid": 59,
    "dateanswer": null,
    "textareaanswer": null,
    "decimalanswer": null,
    "booleananswer": null
  },
  {
    "integeranswer": null,
    "dropdownid": null,
    "questionid": 109,
    "dateanswer": null,
    "textareaanswer": null,
    "decimalanswer": null,
    "booleananswer": false
  }
]

If possible, I'd want to it to be...objectified(?) to have this sort of field structure in the event:

question.59.integeranswer: null
question.59.dropdownid: 47
question.59.dateanswer: null
question.59.textareaanswer: null
question.59.decimalanswer: null
question.59.booleananswer: null

question.109.integeranswer: null
question.109.dropdownid: null
question.109.dateanswer: null
question.109.textareaanswer: null
question.109.decimalanswer: null
question.109.booleananswer: null

2

Do you want stops in the fieldnames or do you want something like

{ "question": { "59" : { "integeranswer": ...
3

I believe what you gave is what I'm looking for.

{
	"question": {
		"59": {
			"integeranswer": "null",
			"dropdownid": "null",
			"dateanswer": "null",
			"textareaanswer": "null",
			"decimalanswer": "null",
			"booleananswer": "null"
		}
	}
}
4

You will need to use ruby. Try

    ruby {
        code => '
            begin
                a = event.get("fieldThatHasAnArrayOfHashes")
                a.each { |x|
                    if x["questionid"]
                        fieldname = "[question][" + x["questionid"].to_s + "]"
                        x.delete("questionid")
                        event.set(fieldname, x)
                    end
                }
            rescue
            end
        '
    }
5

Well that did something, but not quite what is intended, lol

I did this:

ruby {
        code => '
            begin
                a = event.get("question")
                a.each { |x|
                    if x["questionid"]
                        fieldname = "[question][" + x["questionid"].to_s + "]"
                        x.delete("questionid")
                        event.set(fieldname, x)
                    end
                }
            rescue
            end
        '
    }

and this is a partial return of what came out:

question: [
  {
      "integeranswer": null,
      "dropdownid": null,
      "questionid": 222,
      "textanswer": null,
      "textareaanswer": null,
      "decimalanswer": null,
      "booleananswer": true
    },
    {
      "integeranswer": null,
      "dropdownid": null,
      "questionid": 223,
      "textanswer": null,
      "textareaanswer": null,
      "decimalanswer": null,
      "booleananswer": null
    },
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
     - ,
    {
      "integeranswer": null,
      "dropdownid": 1,
      "textanswer": null,
      "textareaanswer": null,
      "decimalanswer": null,
      "booleananswer": null
    },
    {
      "integeranswer": null,
      "dropdownid": null,
      "textanswer": null,
      "textareaanswer": null,
      "decimalanswer": null,
      "booleananswer": false
    },
    {
    "integeranswer": null,
    "dropdownid": null,
    "textanswer": null,
    "textareaanswer": null,
    "decimalanswer": null,
    "booleananswer": null
  }
]
6

Don't modify a field whilst iterating over it.

    ruby {
        code => '
            begin
                a = event.get("question")
                a.each { |x|
                    if x["questionid"]
                        fieldname = "[newQuestion][" + x["questionid"].to_s + "]"
                        x.delete("questionid")
                        event.set(fieldname, x)
                    end
                }
            rescue
            end
        '
    }
    mutate { rename => { "newQuestion" => "question" } }
7

Fantastic, that worked! One last question/request, is there a way to exclude the field if the value is null?

8

I tried adding the below just after what you provided above, but it doesn't seem to work. I've tried v == "" and v == nil, neither seems to work.

      ruby {
        code => '
          event.to_hash.each { |k, v|
            if v.kind_of? String
              if v == nil
                event.remove(k)
              end
            end
          }
        '
      }
9

If you use

 ruby {
        code => '
            begin
                a = event.get("question")
                a.each { |x|
                    if x["questionid"]
                        fieldname = "[newQuestion][" + x["questionid"].to_s + "]"

                        h = {}
                        x.each { |k, v|
                            if v
                                unless k == "questionid"
                                    h[k] = v
                                end
                            end
                        }
                        event.set(fieldname, h)
                    end
                }
            rescue
            end
        '
    }
    mutate { rename => { "newQuestion" => "question" } }

then you will get

  "question" => {
    "109" => {},
     "59" => {
        "dropdownid" => 47
    }
},
1 Like
10

That did it, as always, thanks for the assist @Badger

11

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.