topic Re: Why does REST API return nested json as string not json? in Splunk Search

Why does REST API return nested json as string not json?

bdunstan — Tue, 07 Feb 2023 21:24:46 GMT

Hi,

I am using the REST API to pull data from splunk, using the output_mode=json.

The data that is returned is a mix of strings and JSON (objects) and I am trying to work out a way for the API to return the entire data set as JSON.

For Example:
Curl Command:
curl -k -u 'user1'' https://splunk-server:8089/servicesNS/admin/search/search/jobs/export -d 'preview=false' -d 'output_mode=json' -d 'search=|savedsearch syslog_stats latest="-2d@d" earliest="-3d@d" span=1' | jq .

Results: Note how the result is in JSON, but devices is an array of strings not json.
{
"preview": false,
"offset": 0,
"lastrow": true,
"result": {
    "MsgType": "LINK-3-UPDOWN",
"devices": [
"{\"device\":\"1.1.1.1\",\"events\":12,\"deviceId\":null}",
      "{\"device\":\"2.2.2.2\",\"events\":128,\"deviceId\":1}",
      "{\"device\":\"3.3.3.3\",\"events\":217,\"deviceId\":2}"
],
    "total": "357",
}
}

Query:
| tstats count as events where index=X-syslog Severity<=4 earliest=-3d@d latest=-2d@d by _time, Severity, MsgType Device span=1d
| search MsgType="LINK-3-UPDOWN"
| eval devices=json_object("device", Device, "events", events, "deviceId", deviceId )
| fields - Device events _time Filter UUID Regex deviceId addressDeviceId
| table MsgType devices

Query Result in UI:

MsgType devices total

LINK-3-UPDOWN

{"device":"1.1.1.1","events":12,"deviceId":null}

{"device":"2.2.2.2","events":128,"deviceId":null}

{"device":"3.3.3.3","events":217,"deviceId":null}

357

As can be seen from the UI the device is in JSON format (using json_object), but from the curl result it is a string in json format - is there a way for the query to return the whole result as a json object, not a mix of json and strings ?

I have also tried tojson in a number of differnt ways, but no success.

Desired Result: where devices is a json object and not treated a string as above.
{
"preview": false,
"offset": 0,
"lastrow": true,
"result": {
    "MsgType": "LINK-3-UPDOWN",
    "devices": [
      {"device":"1.1.1.1","events":12,"deviceId":null}",
      {"device":"2.2.2.2","events":128,"deviceId":1}",
      {"device":"3.3.3.3","events":217,"deviceId":2}"
    ],
    "total": "357",
}
}

I can post process the strings into JSON, but I would rather get JSON from SPlunk directly.

Thanks !

Re: Why does REST API return nested json as string not json?

yuanliu — Wed, 08 Feb 2023 11:02:42 GMT

That is because the original data field "devices" contain strings and not JSON objects. (Even though the strings are themselves escaped JSON objects.) For the output to be like your desired results, search UI will give you this instead:

lastrow	offset	preview	result.MsgType	result.devices{}.device	result.devices{}.deviceId	result.devices{}.events	results.total
true	0	false	LINK-3-UPDOWN	1.1.1.1 2.2.2.2 3.3.3.3	null 1 2	12 128 217	357

Escaped JSON string is often employed by considerate developers to avoid unnecessary complexity.

Re: Why does REST API return nested json as string not json?

bdunstan — Wed, 08 Feb 2023 20:56:36 GMT

Thanks.

I make the assumtion (obviously wrong) that the json_object() command created a JSON object and not a string that looks like JSON.

| eval devices=json_object("device", Device, "events", events, "deviceId", deviceId )

Re: Why does REST API return nested json as string not json?

yuanliu — Thu, 09 Feb 2023 08:41:22 GMT

You are not wrong. json_object() creates an escaped JSON string in order to protect the object in ordinarily text contexts. Many log files use this technique, especially when they are already a JSON object but need to embed a text message that includes JSON objects. You just need to remember to unescape it when you need to access JSON.

SPL functions in a text context. Therefore it automatically un-excapes strings created by this function. spath readily recognizes such un-escaped string. You can examine this example,

| makeresults | fields - _time | eval my_message = json_object("key","value") | spath input=my_message

The output will be

key	my_message
value	{"key":"value"}