How handle JSON-Event with associative Array

[Roger_FB]

Hi,
i need a special result, but i dont know how to iterate over an associative array.

Here is this JSON-Events:

Event 1:

{
"created": "28\/May\/2018:06:24:00 +0200",
"response": {           
            "products": {
                "1": {
                    "id": 10,                       
                    "price": 120                        
                },
                "2": {
                    "id": 20,                       
                    "price": 65                     
                },
                "3": {
                    "id": 30,                       
                    "price": 80 
                }
            }
        }       
}

Event 2:

{
"created": "30\/May\/2018:08:10:00 +0200",
"response": {           
            "products": {
                "1": {
                    "id": 40,                       
                    "price": 120                        
                },
                "2": {
                    "id": 50,                       
                    "price": 65                     
                }
            }
        }       
}

And i need the folowing result:

ID      Price
-------------------
10      120
20      65
...      
50      65

Any idea?
Many thanks 🙂

[kamlesh_vaghela]

@Roger_FB

Can you please try this?

YOUR_SEARCH 
| eval id="",price=""
| foreach response.products.*.id 
    [ eval id=id.if(id=="","",if(isnull('<<FIELD>>'),"",",")).if(isnull('<<FIELD>>'),"",'<<FIELD>>')] 
| foreach response.products.*.price 
    [ eval price=price.if(price=="","",if(isnull('<<FIELD>>'),"",",")).if(isnull('<<FIELD>>'),"",'<<FIELD>>')] 
| eval id=split(id,","),price=split(price,","),temp=mvzip(id,price) 
| mvexpand temp 
| table temp | eval id=mvindex(split(temp,","),0),price=mvindex(split(temp,","),1) | table id price

My Sample Search:

| makeresults 
| eval _raw="{ \"created\": \"28\/May\/2018:06:24:00 +0200\", \"response\": {\"products\": { \"1\": { \"id\": 10,\"price\": 120}, \"2\": { \"id\": 20,\"price\": 65}, \"3\": { \"id\": 30,\"price\": 80 } } } }" 
| append 
    [| makeresults 
    | eval _raw="{\"created\": \"30\/May\/2018:08:10:00 +0200\",\"response\": {\"products\": {\"1\": {\"id\": 40,\"price\": 120},\"2\": {\"id\": 50,\"price\": 65}}}}"] 
| kv 
| eval id="",price="" 
| foreach response.products.*.id 
    [ eval id=id.if(id=="","",if(isnull('<<FIELD>>'),"",",")).if(isnull('<<FIELD>>'),"",'<<FIELD>>') ] 
| foreach response.products.*.price 
    [ eval price=price.if(price=="","",if(isnull('<<FIELD>>'),"",",")).if(isnull('<<FIELD>>'),"",'<<FIELD>>') ] 
| eval id=split(id,","),price=split(price,","),temp=mvzip(id,price) 
| mvexpand temp 
| table temp | eval id=mvindex(split(temp,","),0),price=mvindex(split(temp,","),1) | table id price

Thanks

[indigo42]

Hey! Thanks so much for this!! The OP's problem was nearly identical to mine. I'm parsing thru Ansible's win_update JSon and they put in this stupid GUID thing for an object name...anyway...

I didn't know about having to pre populate my field for the foreach! I can't tell you how many hours and hours I spent wondering why, oh why, doesn't my foreach concatonation work???

I am totally stealing this from you. 

| eval upd_kb=""
| foreach ansible_result.filtered_updates.*.kb{} [eval upd_kb=upd_kb.if(upd_kb=="","",if(isnull('<<FIELD>>'),"",",")).if(isnull('<<FIELD>>'),"",'<<FIELD>>') ] 
| table upd_kb

I'm not sure if I'll need the isnull check, but it sure couldn't hurt to have!

Thanks!!

J

[martin_mueller]

The best approach would be to store arrays as arrays. Once you have that, you can use this to get to the individual array elements:

 | spath response.products | mvexpand response.products | spath input=response.products

The way your data is structured right now is that you have unknown/unbounded field/object names. Without known field/object names, how do you access fields/objects?