topic Re: Extract JSON objects in Splunk Search

Extract JSON objects

vishaltaneja070 — Thu, 16 Sep 2021 11:59:19 GMT

How can i extract this:
"properties": {"nextLink": null,
"columns": [
{"name": "Cost", "type": "Number"},
{"name": "Date", "type": "Number"},
{"name": "Charge", "type": "String"},
{"name": "Publisher", "type": "String"},
{"name": "Resource", "type": "String"},
{"name": "Resource", "type": "String"},
{"name": "Service", "type": "Array"},
{"name": "Standard", "type": "String"},
"rows": [
[2.06, 20210807, "usage", "uuuu", "hhh", "gd", "bandwidth", "[app:"new","type":"band"]", "HHH"],
[2.206, 20210807, "usage", "uuuhhh", "ggg", "gd", "bandwidth", "[app:"old","type":"land"]", "YYY"] ]

No of columns can be increased.

@ITWhisperer Can you help?

Re: Extract JSON objects

ITWhisperer — Thu, 16 Sep 2021 12:19:46 GMT

You posted about this a month ago - did this solution not work?

https://community.splunk.com/t5/Splunk-Search/Extract-JSON-objects/m-p/562583#M196043

Re: Extract JSON objects

vishaltaneja070 — Thu, 16 Sep 2021 12:27:21 GMT

@ITWhisperer

Its looks same but its different,
now it has json object inside rows also:

"[app:"new","type":"band"]",

which is causing issue.

Re: Extract JSON objects

ITWhisperer — Thu, 16 Sep 2021 12:58:21 GMT

The issue is note to do with it trying to be a JSON object (app should be in double quotes to be JSON), the problem is that there are embedded quotes in the string. Can you get the format changed to escape the embedded quotes with a backslash?

Re: Extract JSON objects

vishaltaneja070 — Thu, 16 Sep 2021 13:20:32 GMT

@ITWhisperer
Sorry app is also in double quotes only
"properties": {"nextLink": null,
"columns": [
{"name": "Cost", "type": "Number"},
{"name": "Date", "type": "Number"},
{"name": "Charge", "type": "String"},
{"name": "Publisher", "type": "String"},
{"name": "Resource", "type": "String"},
{"name": "Resource", "type": "String"},
{"name": "Service", "type": "Array"},
{"name": "Standard", "type": "String"},
"rows": [
[2.06, 20210807, "usage", "uuuu", "hhh", "gd", "bandwidth", ["app":"new","type":"band"], "HHH"],
[2.206, 20210807, "usage", "uuuhhh", "ggg", "gd", "bandwidth", ["app":"old","type":"land"], "YYY"] ]

Re: Extract JSON objects

ITWhisperer — Thu, 16 Sep 2021 14:38:51 GMT

This is not valid JSON syntax, the columns collection should be closed with a right brace and the embedded collection should be converted to an object e.g.

{"properties": {"nextLink": null, "columns": [ {"name": "Cost", "type": "Number"}, {"name": "Date", "type": "Number"}, {"name": "Charge", "type": "String"}, {"name": "Publisher", "type": "String"}, {"name": "Resource", "type": "String"}, {"name": "Resource", "type": "String"}, {"name": "Service", "type": "Array"}, {"name": "Standard", "type": "String"} ], "rows": [ [2.06, 20210807, "usage", "uuuu", "hhh", "gd", "bandwidth", {"app":"new","type":"band"}, "HHH"], [2.206, 20210807, "usage", "uuuhhh", "ggg", "gd", "bandwidth", {"app":"old","type":"land"}, "YYY"] ] }

Please can you check your real data?

Re: Extract JSON objects

vishaltaneja070 — Thu, 16 Sep 2021 15:51:16 GMT

@ITWhisperer Sorry my bad, wrong json. can you please try now. I have tested this json on jsonviewer.

{"properties": {"nextLink": null, "columns": [ {"name": "Cost", "type": "Number"}, {"name": "Usage", "type": "Number"}, {"name": "Charge", "type": "String"}, {"name": "Type", "type": "String"}, {"name": "Res", "type": "String"}, {"name": "ResGF", "type": "String"}, {"name": "Name", "type": "String"}, {"name": "Reso", "type": "String"}, {"name": "Tag", "type": "Array"}, {"name": "Wallet", "type": "String"}], "rows": [[0.01564, 20200708, "usage", "gcp", "unknown", "hhhh", "newwww", "zyyyyy", ["\"app\":\"hhh\"", "\"created\":\"hhh\""], "USD"],[0.064, 20200708, "usage", "gcp", "new", "zyy", "hhh", "zyyyyy", ["\"app\":\"work\"", "\"hum\":\"zhhh\""], "USD"]]}}

Re: Extract JSON objects

vishaltaneja070 — Thu, 16 Sep 2021 15:51:52 GMT

@ITWhisperer Sorry my bad, wrong json. can you please try now. I have tested this json on jsonviewer.

Re: Extract JSON objects

vishaltaneja070 — Fri, 17 Sep 2021 12:52:26 GMT

@ITWhisperer Just tagged you if you have seen my response

Re: Extract JSON objects

ITWhisperer — Fri, 17 Sep 2021 14:13:39 GMT

So the issue is with the embedded array and knowing which "column" it is in, and knowing how many elements there are in each instance of the array. spath can extract it e.g. rows.{}{}{} but they all end up in the same mv field and you can't tell how many belong to the first row and how many belong to the second row etc. You would also have to inspect the type fields for the column to work out which field requires an array of elements. I haven't figured out a way to do this.