# Parsing JSON Array In Event

**URL:** <https://discuss.elastic.co/t/parsing-json-array-in-event/328393>\
**Category:** Logstash\
**Created:** [March 23, 2023, 8:18pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393 "2023-03-23T20:18:17Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\
**Post date:** [March 23, 2023, 8:18pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393/1 "2023-03-23T20:18:17Z")

</div>

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?

```auto
[
  {
    "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

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [March 23, 2023, 10:39pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393/2 "2023-03-23T22:39:17Z")

</div>

> [@wwalker](#):
>
> question.59.integeranswer

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

```
{ "question": { "59" : { "integeranswer": ...

```

---

<div class="post-metadata">

**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\
**Post date:** [March 24, 2023, 2:25pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393/3 "2023-03-24T14:25:50Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [March 24, 2023, 4:11pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393/4 "2023-03-24T16:11:29Z")

</div>

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
        '
    }

```

---

<div class="post-metadata">

**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\
**Post date:** [March 24, 2023, 5:00pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393/5 "2023-03-24T17:00:38Z")

</div>

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

I did this:

```auto
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:

```auto
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
  }
]

```

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [March 24, 2023, 5:43pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393/6 "2023-03-24T17:43:52Z")

</div>

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" } }

```

---

<div class="post-metadata">

**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\
**Post date:** [March 24, 2023, 6:41pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393/7 "2023-03-24T18:41:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\
**Post date:** [March 24, 2023, 9:33pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393/8 "2023-03-24T21:33:42Z")

</div>

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.

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

```

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [March 24, 2023, 9:39pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393/9 "2023-03-24T21:39:23Z")

</div>

> [@wwalker](#):
>
> One last question/request, is there a way to exclude the field if the value is null?

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
    }
},

```

---

<div class="post-metadata">

**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\
**Post date:** [March 24, 2023, 9:54pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393/10 "2023-03-24T21:54:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 21, 2023, 9:55pm UTC](https://discuss.elastic.co/t/parsing-json-array-in-event/328393/11 "2023-04-21T21:55:17Z")

</div>

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