Count fields from json file is not considering the repeating values

[anooshac]

Hi all,

I have a json file in the format,

{ "NUM":"5",

"EXECUTION_DATE":04-07-2022,

"STATUS":"FAILURE",

"DURATION":5 hrs, 13 mins,

"PARTS":[

{ "NAME":"abc",

"PART_NO":[ "2634702", "2634456","2634890",] },

{ "NAME":"xyz",

"PART_NO":[ "2634702", ] },

] }

I wanted to calculate the count of PART_NO and plot it in a chart. The PART_NO are repeating and i want to calculate the repeated value also, i used count here. I used |timechart count(PARTS{}.PART_NO{}) but it is giving wrong count. Is there any different method to calculate the count?

[gcusello]

Hi @anooshac,

rename it or use before timechart or use quotes: sometimes with json fields there's some problem:

| rename  "NAME{}.PART_NO{}" AS PART_NO
| rimechart count BY PART_NO

Ciao.

Giuseppe

[anooshac]

I want to get the chart for 2 data . If i use count by will i be able to get the data for 2 charts?

I used | timechart span=1d distinct_count(NUM), count(NAME{}.PART_NO{})

I used the solution you provided but the count is still not matching with the data i uploaded. The reeating values are not being considered. How to consider the repeating values for the count?

[ITWhisperer]

It should be PARTS not NAME

| timechart count by PARTS{}.PART_NO{}

That being said, in what way are the counts "wrong"?

[anooshac]

Sorry i used the same. The count i got from the query does not match with the data i uploaded. The repeating values of the PART_NO are not considered eventhough i used count. How to consider the repeating values? Also  I wanted to plot chart for both NUM and PART_NO, used | timechart span=1d distinct_count(NUM), count(NAME{}.PART_NO{}). Used the solution given by you also. Still showing the same result.