topic Re: How to create a table from nested JSON keys with different names? in Splunk Search

How to create a table from nested JSON keys with different names?

DenysB — Wed, 14 Feb 2018 17:32:42 GMT

Part of my json event looks like this:

1. "certificatecache":[
2. {"type":"cacheSize","int32value":"10"},
3. {"type":"cacheInUse","int32value":"0"},
4. {"type":"certInCache","int32value":"1"},
5. {"type":"hit","int64gap":"1428335"},
6. {"type":"miss","int64gap":"79397"},
7. {"type":"health","int32value":"100"}
8. ]

I get fields certificatecache{}.type, certificatecache{}.int32value, certificatecache{}.int64gap and try to use spath, but if you notice, both of fields certificatecache{}.int32value and certificatecache{}.int64gap contain certificatecache values and it is a problem

I'd like to create a Table with certificatecache_type certificatecache_value.

Thanks a lot in advance!

Re: How to create a table from nested JSON keys with different names?

mdsnmss — Wed, 14 Feb 2018 18:25:31 GMT

You can try to use the rename to your advantage: | rename certificatecache{}.type as certificatecache_type, certificatecache{}.int* as certificatecache_value. Renaming can help manipulating JSON arrays easier.

Re: How to create a table from nested JSON keys with different names?

DenysB — Tue, 29 Sep 2020 18:04:01 GMT

I got an error:
Error in 'rename' command: Wildcard mismatch: 'certificatecache{}.int*' as 'certificatecache_value'.

In this case, as I understand, I should use:
| rename certificatecache{}.type as certificatecache_type, certificatecache{}.int* as certificatecache_value*
but it doesn't make sense.

Re: How to create a table from nested JSON keys with different names?

mdsnmss — Thu, 15 Feb 2018 13:03:38 GMT

It looks like you have to rename the JSON array first. Try: | rename certificatecache{}.* as * | rename type as certificate_type int* as certificate_value

Re: How to create a table from nested JSON keys with different names?

DenysB — Fri, 16 Feb 2018 09:05:58 GMT

The same:
Error in 'rename' command: Wildcard mismatch: 'int*' as 'certificate_value'.

I guess it's a wrong way to use rename.

http://docs.splunk.com/Documentation/Splunk/7.0.2/SearchReference/Rename
"You cannot use the rename command to merge multiple fields into one field because of null, or non-present, fields are brought along with the values."

Re: How to create a table from nested JSON keys with different names?

mdsnmss — Fri, 16 Feb 2018 12:19:08 GMT

Oh, I see now. Yeah, it is trying to combine two fields into one with that. A couple of questions: Are there multiple events like this? You are trying to make a table with multiple rows for the single event, correct? Like:

certificatecache_type                certificatecache_value
cacheSize                                    10
cacheInUse                                 0
certInCache                                1
hit                                             1428335
miss                                          79397
health                                       100

Re: How to create a table from nested JSON keys with different names?

DenysB — Sun, 18 Feb 2018 11:30:00 GMT

Yes, you are right, I am trying to make this table.

Re: How to create a table from nested JSON keys with different names?

493669 — Sun, 18 Feb 2018 17:09:13 GMT

Hi @DenysB,
try coalesce function after getting 3 columns:

...| eval certificatecache_value=coalesce('certificatecache{}.int32value','certificatecache{}.int64gap')

Re: How to create a table from nested JSON keys with different names?

DenysB — Wed, 21 Feb 2018 07:51:49 GMT

Hi!
In this case, coalesce doesn't work, because it returns the first value that is not NULL and I get only or int32value or int64gap values.

Re: How to create a table from nested JSON keys with different names?

493669 — Wed, 21 Feb 2018 07:56:24 GMT

In each event either int32value or int64gap values will be present but not both in a single event...isn't it?

Re: How to create a table from nested JSON keys with different names?

DenysB — Thu, 22 Feb 2018 09:11:44 GMT

No, it is a key problem. I have both int32value and int64gap in a single event.