How to sort JSON Array using raw data?

bora9 — Tue, 29 Sep 2020 18:15:56 GMT

Hello I've been trying to chart/table the following search but I keep getting the wrong sorting for my array.

My search :

source="rest://Energy Lifetime" 
| spath input=_raw output=production path=production{} 
| head 1
| stats latest(production) by production 
| table meter_start_date, production

My raw:

{"system_id":0023251,"start_date":"2017-09-14","production":[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,10166,8951,8954,5360,9469,9871,9045,8360,5584,9431,9523,9307,8996,6431,4364,9331,5930,4394,9321,8785,8235,4023,9417,8006,4370,8229,2764,9600,9337,4105,6717,8890,9576,9483,10282,9552,4689,6193,9013,3702,8725,3724,9574,3831,7774,10389,3462,9767,10800,9055,4421,7062,2751,10919,11605,9039,7970,5843,10982,10377,11656,3383,8779,10129,12253,11515,5248,11817,10511,13125,10561,7534,246,1601,1460,11066,8822,10814,15167,15136,10716,1912,1235,5973,11124,7391,16624,16954],"meter_start_date":"2017-09-14","meta":{"status":"normal","last_report_at":1519660861,"last_energy_at":1519660800,"operational_at":1505428786}}

The sorting Im expecting is exactly how it shows on the raw for production (0,
0,
0,
... ,
16624,
16954)

but I keep getting the following sorting (0,
10129,
10166,
10282,
...,
9576,
9600,
9767,
9871
)

Can anybody please help as I have been trying to do this without any luck.

UPDATE: Now I want to be able to give the correct dates to the table but since the raw data only includes the start date I would have to generate all dates in a consecutive order. I have tried the following:

| gentimes start=9/14/2017
| eval Date=strftime(endtime,"%m/%d/%Y") 
| table Date

Which will give me a consecutive dates from 9/14/2017 until now but I can't seem to manage to replace meter_start_date with these new values

what I tried so far

source="rest://Energy Lifetime" 
| spath input=_raw output=production path=production{} 
| head 1
| table Date production 
| join [| gentimes start=9/14/2017
| eval Date=strftime(endtime,"%m/%d/%Y") 
| fields Date
] 
| mvexpand production
| where production > 0

This is yielding all days to be the same. I think it is due to the "head 1" but not quite sure how to integrate it in order to keep the same order

FINAL ANSWER:

Managed to get it to work now by doing the following:

source="rest://Energy Lifetime" 
| spath input=_raw output=production path=production{} 
| head 1
| mvexpand production
| table Date production 
| appendcols [| gentimes start=9/14/2017
| eval Date=strftime(endtime,"%m/%d/%Y") 
| fields Date
] 
| where production > 0

This will plot all the dates ranging from 9/14/2017 until today along with the production generated each day.

Re: How to sort JSON Array using raw data?

somesoni2 — Mon, 26 Feb 2018 22:30:45 GMT

Give this a try

source="rest://Energy Lifetime" 
 | spath   | head 1 | rename production{} as production
| table meter_start_date production | mvexpand production

Re: How to sort JSON Array using raw data?

bora9 — Tue, 29 Sep 2020 18:16:02 GMT

That search ended up providing the same answer twice in the correct order. Ex. (0,
0,
0,
... ,
16624,
16954)

then repeated itself again.

Your search guided me towards the correct idea and ended up using the following search to chart correctly:

source="rest://Energy Lifetime"
| spath input=_raw output=production path=production{}
| head 1
| table meter_start_date production
| mvexpand production

Thank you very much for the help!

topic Re: How to sort JSON Array using raw data? in Getting Data In

How to sort JSON Array using raw data?

Re: How to sort JSON Array using raw data?

Re: How to sort JSON Array using raw data?