topic Re: Zip values from JSON. in Getting Data In

Zip values from JSON.

sandeepmakkena — Wed, 30 Sep 2020 02:11:46 GMT

I want to get the total units by PartNumber. I tried using spath but it didnt work maybe I am doing something wrong, this is my 1st time work with Json bodies in Splunk, can someone help. Thanks.

{ [-]
app: { [+]
}
dtm: 2019-09-16 11:21:30.780 PDT
logger: transaction.com.WEB_ORDER_CREATE
msg: { [-]
data: { [-]
cartId: 00000000-784f-642d-d000-0000784f142d
casId: a493ff7e-2d87-416c-ba08-0663107e958c
clientSubType: web.common
clientType: web
cosId: 80f1a259-7187-4f01-9c85-acd7e054233d
deliveryGroup: SINGLE_ADDRESS
features: [ [-]
crd
]
fraudDecision: NF
lineItems: [ [-]
{ [-]
commitCode: 0
deliveryDate: Wed, Sep 18
deliveryType: STH
partNumber: MRYR2LL/A
product: Phone 64GB Black
qty: 2
resolvedDate: Wed, Sep 18
shipMethodCode: E2
}
{ [-]
commitCode: 0
deliveryDate: Within 5 days after shipping
deliveryType: STH
partNumber: MRW62ZM/A
product: Phone Case
qty: 1
resolvedDate: In Stock
shipMethodCode: UG
}
]
ops.response: SUCCEEDED
orderType: order
payments: [ [+]
]
pssId: Wb90d652a55994842b0474288ba0dea76
remoteHost: 17.123.345
storeFront: { [+]
}
webOrderNumber: W12345678
}
headerData: { [+]
}
}
}

Re: Zip values from JSON.

kamlesh_vaghela — Tue, 17 Sep 2019 05:24:48 GMT

@sandeepmakkena

Can you please share valid JSON event into code block?

Re: Zip values from JSON.

poete — Tue, 08 Oct 2019 15:25:04 GMT

Hello @sandeepmakkena,

please find below the query that results in a table containing the number of partNumber per qty.

|makeresults|eval _raw="{
    \"app\": {
        \"foo1\": \"bar1\"
    },
    \"dtm\": \"2019-09-16 11:21:30.780 PDT\",
    \"logger\": \"transaction.com.WEB_ORDER_CREATE\",
    \"msg\": {
        \"data\": {
            \"cartId\": \"00000000-784f-642d-d000-0000784f142d\",
            \"casId\": \"a493ff7e-2d87-416c-ba08-0663107e958c\",
            \"clientSubType\": \"web.common\",
            \"clientType\": \"web\",
            \"cosId\": \"80f1a259-7187-4f01-9c85-acd7e054233d\",
            \"deliveryGroup\": \"SINGLE_ADDRESS\",
            \"features\": [\"crd\"],
            \"fraudDecision\": \"NF\",
            \"lineItems\": [{
                    \"commitCode\": 0,
                    \"deliveryDate\": \"Wed, Sep 18\",
                    \"deliveryType\": \"STH\",
                    \"partNumber\": \"MRYR2LL/A\",
                    \"product\": \"Phone 64GB Black\",
                    \"qty\": 2,
                    \"resolvedDate\": \"Wed, Sep 18\",
                    \"shipMethodCode\": \"E2\"
                },
                {
                    \"commitCode\": 0,
                    \"deliveryDate\": \"Within 5 days after shipping\",
                    \"deliveryType\": \"STH\",
                    \"partNumber\": \"MRW62ZM/A\",
                    \"product\": \"Phone Case\",
                    \"qty\": 1,
                    \"resolvedDate\": \"In Stock\",
                    \"shipMethodCode\": \"UG\"
                }
            ],
            \"ops.response\": \"SUCCEEDED\",
            \"orderType\": \"order\",
            \"payments\": [{
                \"foo2\": \"bar2\"
            }],
            \"pssId\": \"Wb90d652a55994842b0474288ba0dea76\",
            \"remoteHost\": \"17.123.345\",
            \"storeFront\": {
                \"foo3\": \"bar3\"
            },
            \"webOrderNumber\": \"W12345678\"
        },
        \"headerData\": {
            \"foo4\": \"bar4\"
        }
    }
}"|spath |rename msg.data.lineItems{}.partNumber as partNumber msg.data.lineItems{}.qty as qty |table partNumber, qty| eval x=mvzip(partNumber, qty)|table x |mvexpand x
  | eval x = split(x,",")|eval partNumber = mvindex(x,0)|eval qty=mvindex(x,1)|table partNumber,qty