# How to extract a value from a JSON string painless

**URL:** <https://discuss.elastic.co/t/how-to-extract-a-value-from-a-json-string-painless/121136>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-alerting\
**Created:** [February 22, 2018, 10:01pm UTC](https://discuss.elastic.co/t/how-to-extract-a-value-from-a-json-string-painless/121136 "2018-02-22T22:01:25Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![saranyav](https://avatars.discourse-cdn.com/v4/letter/s/977dab/32.png) [@saranyav](https://discuss.elastic.co/u/saranyav)\
**Post date:** [February 22, 2018, 10:01pm UTC](https://discuss.elastic.co/t/how-to-extract-a-value-from-a-json-string-painless/121136/1 "2018-02-22T22:01:25Z")

</div>

Hello,

I have a document where a field request.content comes in as a JSON string. In which I need to parse the Primary Email value in a scripted field using painless scripting.

I am new to painless scripting, can anyone guide me where to start with?

"\_source": {  
"request": {  
"content": "{"eventId":0,"guid":"7893f0e1-7e96-41b6-8b97-324b5ee58d10","customerId":28795699,"code":"RUWPI","requestId":"E5C3BB1C5102E52C8436943B6ADEBCBE","username":"browne1941","sessionId":"E5C3BB1C5102E52C8436943B6ADEBCBE","serverIp":"000.000.000.1","requestor":"ControlPanel","employeeId":"kakrueger","requestIp":"000.000.000.1","remoteIp":"000.000.000.1","timestamp":1518110219083,"details":[{"eventId":0,"valueDescription":"First Name","value":"graeme","oldValue":null,"message1":null,"message2":null},{"eventId":0,"valueDescription":"Last Name","value":"browne","oldValue":null,"message1":null,"message2":null},{"eventId":0,"valueDescription":"Role Id","value":"INDIVIDUAL","oldValue":null,"message1":null,"message2":null},{"eventId":0,"valueDescription":"Primary [Email","value":"sara85@hotmail.com](mailto:Email%22,%22value%22:%22sara85@hotmail.com)","oldValue":null,"message1":null,"message2":null},{"eventId":0,"valueDescription":"Alternate Email","value":null,"oldValue":null,"message1":null,"message2":null}]}"  
}}

Thanks!  
Saranya

---

<div class="post-metadata">

**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\
**Post date:** [February 23, 2018, 1:29am UTC](https://discuss.elastic.co/t/how-to-extract-a-value-from-a-json-string-painless/121136/2 "2018-02-23T01:29:37Z")

</div>

If I use Kibana \> Dev Tools \> Console I can post this data to a new index like this;

```auto
POST discuss/test
{
  "request": {
    "content": {
      "eventId": 0,
      "guid": "7893f0e1-7e96-41b6-8b97-324b5ee58d10",
      "customerId": 28795699,
      "code": "RUWPI",
      "requestId": "E5C3BB1C5102E52C8436943B6ADEBCBE",
      "username": "browne1941",
      "sessionId": "E5C3BB1C5102E52C8436943B6ADEBCBE",
      "serverIp": "000.000.000.1",
      "requestor": "ControlPanel",
      "employeeId": "kakrueger",
      "requestIp": "000.000.000.1",
      "remoteIp": "000.000.000.1",
      "timestamp": 1518110219083,
      "details": [
        {
          "eventId": 0,
          "valueDescription": "First Name",
          "value": "graeme",
          "oldValue": null,
          "message1": null,
          "message2": null
        },
        {
          "eventId": 0,
          "valueDescription": "Last Name",
          "value": "browne",
          "oldValue": null,
          "message1": null,
          "message2": null
        },
        {
          "eventId": 0,
          "valueDescription": "Role Id",
          "value": "INDIVIDUAL",
          "oldValue": null,
          "message1": null,
          "message2": null
        },
        {
          "eventId": 0,
          "valueDescription": "Primary Email",
          "value": "sara85@hotmail.com",
          "oldValue": null,
          "message1": null,
          "message2": null
        },
        {
          "eventId": 0,
          "valueDescription": "Alternate Email",
          "value": null,
          "oldValue": null,
          "message1": null,
          "message2": null
        }
      ]
    }
  }
}
}

```

Then I create an index pattern. If I filter the list of fields to those containing "details" I see this;

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/6/b6fc58b7d778fa0738f9a345056b435966e55278.png)

Is this about what you see? If not can you post a screenshot here?

If I create a String type scripted field with this script;  
`doc['request.content.details.value.keyword'].value`

I seem to get a random "value" in Discover tab; like either `INDIVIDUAL` or `graeme`

Same if I just create a Data Table visualization with a Terms aggregation on `request.content.details.value.keyword`

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/1/a190f3ec783a685318681d7ce2a556decc42c4c3.png)

I think you might be able to do better if you can first set the mapping. But I'll wait for your feedback before I spend more time on it.

Regards,  
Lee

---

<div class="post-metadata">

**Author:** ![saranyav](https://avatars.discourse-cdn.com/v4/letter/s/977dab/32.png) [@saranyav](https://discuss.elastic.co/u/saranyav)\
**Post date:** [February 23, 2018, 1:43am UTC](https://discuss.elastic.co/t/how-to-extract-a-value-from-a-json-string-painless/121136/3 "2018-02-23T01:43:35Z")

</div>

Thanks for the response, LeeDr.

I posted this data in kibana and I created Index pattern. Here is the screen shot.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/c/7ccfbe9dca1ad622bb7a3fb853e66ca0d31c5422.png).

But in my original index, the doc is only indexed for request.content and request.content.keyword . The contents of request.content is coming in as a string. Just wondering if I can get the "username" or "email" which are part of request.content STRING using scripted fields.

Here is my original Index pattern.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/1/6/16325e66f4700868ddc5fd5c78a39787477c9500.png)

**UPDATE** : When I surround the data inside request.content with double quotes, the document only indexes request.content and request.content.keyword

"request": {  
"content": "{"eventId":0.....}"  
}

But when I take those quotes surrounding request.content off, all the fields under request.content are indexed. request.content.eventId,..etc

"request": {  
"content": {"eventId":0.....}  
}

Now that the data is already indexed and my original index only has request.content and request.content.keyword, looking for ways to extract the fields under request.content in a scripted field.

Thanks!  
SV

---

<div class="post-metadata">

**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\
**Post date:** [February 23, 2018, 4:16pm UTC](https://discuss.elastic.co/t/how-to-extract-a-value-from-a-json-string-painless/121136/4 "2018-02-23T16:16:00Z")

</div>

You could certainly try to create a scripted field to get it. It can be hard to get scripted fields working correctly. So my advice is to work incrementally towards your solution.

First create a numeric scripted field which just shows the length of that `request.content.keyword` field and verify it works in Discover.

Then create another numeric scripted field which returns the index of the string "Primary Email" within `request.content.keyword`

You might be able to extract the email address that way.

If you enable regular expressions that might make it a bit easier also.

---

<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:** [March 23, 2018, 4:16pm UTC](https://discuss.elastic.co/t/how-to-extract-a-value-from-a-json-string-painless/121136/5 "2018-03-23T16:16:02Z")

</div>

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