Extract JSON objects

[vishaltaneja070]

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?

[vishaltaneja070]

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

[ITWhisperer]

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 

[vishaltaneja070]

@ITWhisperer 

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

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

which is causing issue.

[ITWhisperer]

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?

[vishaltaneja070]

@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"] ]

[ITWhisperer]

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?

[vishaltaneja070]

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

[vishaltaneja070]

@ITWhisperer Just tagged you if you have seen my response

[ITWhisperer]

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.