topic How can I format the JSON file? in Getting Data In

How can I format the JSON file?

gdermiliis — Fri, 20 Sep 2019 16:05:36 GMT

Hi all,

I have loaded a JSON file from API interface. I have this JSON structure:

{
"productName": "ORACLE RDBMS",
"supplier": {
"supplierName": "ORACLE"
},
"components": [
{
"componentName": "ORACLE CLIENT",
"versions": [
{
"version": "12.1.0.1",
"gaDate": "2013-07-01",
"eosDate": "2015-07-22",
"versionScore": {
"current": 0,
"predicition6M": 0,
"predicition12M": 0
}
},
{
"version": "12.1.0.2",
"gaDate": "2014-07-01",
"eosDate": "2021-07-01",
"versionScore": {
"current": 4,
"predicition6M": 3
"predicition12M": 3
}
},
]

{
"componentName": "ORACLE DATABASE",
"versions": [
{
"version": "8.1",
"gaDate": "2000-09-01",
"eosDate": "2005-01-01",
"versionScore": {
"current": 2,
"predicition6M": 1,
"predicition12M": 0
}
},
{
"version": "9.2",
"gaDate": "2002-07-01",
"eosDate": "2007-08-01",
"versionScore": {
"current": 2,
"predicition6M": 2,
"predicition12M": 1,
}
},
{
"version": "10.1",
"gaDate": "2004-01-01",
"eosDate": "2009-01-01",
"versionScore": {
"current": 4,
"predicition6M": 4,
"predicition12M": 3
}
},

     ]
  }

]
}

and I need to create this table:

ProductName ComponentName Version eosDate current predicition6M predicition12M
ORACLE RDBMS ORACLE CLIENT 12.1.0.1 22/07/2015 0 0 0
ORACLE RDBMS ORACLE CLIENT 12.1.0.2 01/07/2021 4 3 3
ORACLE RDBMS ORACLE DATABASE 8.1 01/01/2005 2 1 0
ORACLE RDBMS ORACLE DATABASE 9.2 01/08/2007 2 2 1
ORACLE RDBMS ORACLE DATABASE 10.1 01/01/2009 4 4 3

How can I do?
Tks
Gianluca

Re: How can I format the JSON file?

richgalloway — Fri, 20 Sep 2019 16:38:47 GMT

What have you tried so far? What results did you get?

Re: How can I format the JSON file?

gdermiliis — Wed, 30 Sep 2020 02:19:32 GMT

I have tried with these search commands:

index="test" source="test.json" productName="ORACLE RDBMS" | rename components.versions.version AS VERSION, components.versions.versionScore.obsolescenceDate AS OBSDATE, components.componentName AS COMP_NAME, productName AS PROD_NAME | spath| eval a=mvzip(COMP_NAME,VERSION,OBSDATE)
| mvexpand a | eval a = split(a,",") | eval COMP_NAME=mvindex(a,0) | eval VERSION=mvindex(a,1) | eval OBSDATE=mvindex(a,2) |table product, Name,COMP_NAME,VERSION,OBSDATE

the result is:

product Name COMP_NAME VERSION OBSDATE
18/09/2019 15:40 ORACLE CLIENT 12.1.0.1
18/09/2019 15:40 ORACLE DATABASE 12.1.0.2
18/09/2019 15:40 DATAGUARD 12.2.1.2