How to extract a value from a JSON string painless

[saranyav]

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","oldValue":null,"message1":null,"message2":null},{"eventId":0,"valueDescription":"Alternate Email","value":null,"oldValue":null,"message1":null,"message2":null}]}"
}}

Thanks!
Saranya

[LeeDr (Lee Drengenberg)]

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

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;

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

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

[saranyav]

Thanks for the response, LeeDr.

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

.

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.

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

[LeeDr (Lee Drengenberg)]

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.