Filtering JSON data with jq

Hello.

I have the following JSON data:

[
  {
    "month": "Jan",
    "MTX": "10.4",
    "MTN": "3.8",
    "MTT": "7.1",
    "TXX": "19.5",
    "D_TXX": "27/2003",
    "TNN": "-4.2",
    "D_TNN": "10/2009",
    "MNDTX35": "0",
    "MNDTX30": "0",
    "MNDTX25": "0",
    "MNDTN20": "0",
    "MNDTN0": "3.5",
    "MRR": "183.2",
    "RRX": "84.3",
    "D_RRX": "3/2003",
    "MNDRR01": "",
    "MNDRR1": "12.1",
    "MNDRR10": "5.9",
    "MNDRR20": "3.2",
    "MNDRR30": "1.9"
  },
  {
    "month": "Fev",
    "MTX": "12.3",
    "MTN": "4.5",
    "MTT": "8.4",
    "TXX": "21.3",
    "D_TXX": "28/1997",
    "TNN": "-4.5",
    "D_TNN": "5/1994",
    "MNDTX35": "0",
    "MNDTX30": "0",
    "MNDTX25": "0",
    "MNDTN20": "0",
    "MNDTN0": "2.6",
    "MRR": "103.5",
    "RRX": "60.3",
    "D_RRX": "7/2001",
    "MNDRR01": "",
    "MNDRR1": "8.8",
    "MNDRR10": "3.4",
    "MNDRR20": "1.7",
    "MNDRR30": "0.7"
  },
  {
    "month": "Mar",
    "MTX": "15.5",
    "MTN": "6.4",
    "MTT": "11",
    "TXX": "25.8",
    "D_TXX": "22/2002",
    "TNN": "-6.8",
    "D_TNN": "1/2005",
    "MNDTX35": "0",
    "MNDTX30": "0",
    "MNDTX25": "0.1",
    "MNDTN20": "0",
    "MNDTN0": "1.2",
    "MRR": "98.2",
    "RRX": "67.4",
    "D_RRX": "2/2001",
    "MNDRR01": "",
    "MNDRR1": "8.3",
    "MNDRR10": "3.5",
    "MNDRR20": "1.5",
    "MNDRR30": "0.7"
  },
  {
    "month": "Abr",
    "MTX": "16.4",
    "MTN": "7.1",
    "MTT": "11.8",
    "TXX": "28.3",
    "D_TXX": "30/1994",
    "TNN": "-1.2",
    "D_TNN": "20/1995",
    "MNDTX35": "0",
    "MNDTX30": "0",
    "MNDTX25": "1.9",
    "MNDTN20": "0",
    "MNDTN0": "0.1",
    "MRR": "118.7",
    "RRX": "61.3",
    "D_RRX": "8/2008",
    "MNDRR01": "",
    "MNDRR1": "11.1",
    "MNDRR10": "4.1",
    "MNDRR20": "1.5",
    "MNDRR30": "0.6"
  },
  {
    "month": "Mai",
    "MTX": "19.4",
    "MTN": "9.6",
    "MTT": "14.5",
    "TXX": "33.2",
    "D_TXX": "29/2001",
    "TNN": "1.8",
    "D_TNN": "1/2001",
    "MNDTX35": "0",
    "MNDTX30": "0.7",
    "MNDTX25": "5.1",
    "MNDTN20": "0.4",
    "MNDTN0": "0",
    "MRR": "98.2",
    "RRX": "74.6",
    "D_RRX": "18/1996",
    "MNDRR01": "",
    "MNDRR1": "10.4",
    "MNDRR10": "3.6",
    "MNDRR20": "1",
    "MNDRR30": "0.3"
  },
  {
    "month": "Jun",
    "MTX": "24.4",
    "MTN": "13",
    "MTT": "18.7",
    "TXX": "35.4",
    "D_TXX": "21/2001",
    "TNN": "5.6",
    "D_TNN": "9/1992",
    "MNDTX35": "0.2",
    "MNDTX30": "4.6",
    "MNDTX25": "14.5",
    "MNDTN20": "1.1",
    "MNDTN0": "0",
    "MRR": "37.6",
    "RRX": "70",
    "D_RRX": "1/1998",
    "MNDRR01": "",
    "MNDRR1": "4.6",
    "MNDRR10": "0.8",
    "MNDRR20": "0.5",
    "MNDRR30": "0.3"
  },
  {
    "month": "Jul",
    "MTX": "27.6",
    "MTN": "14.5",
    "MTT": "21.1",
    "TXX": "39.4",
    "D_TXX": "24/1995",
    "TNN": "7.3",
    "D_TNN": "3/1997",
    "MNDTX35": "1.9",
    "MNDTX30": "10.6",
    "MNDTX25": "21.9",
    "MNDTN20": "3.4",
    "MNDTN0": "0",
    "MRR": "19.5",
    "RRX": "38.4",
    "D_RRX": "25/2000",
    "MNDRR01": "",
    "MNDRR1": "2.6",
    "MNDRR10": "0.7",
    "MNDRR20": "0.1",
    "MNDRR30": "0.1"
  },
  {
    "month": "Ago",
    "MTX": "28",
    "MTN": "15.3",
    "MTT": "21.6",
    "TXX": "38.6",
    "D_TXX": "8/2003",
    "TNN": "7.6",
    "D_TNN": "25/1993",
    "MNDTX35": "2.2",
    "MNDTX30": "11.5",
    "MNDTX25": "22.8",
    "MNDTN20": "4.2",
    "MNDTN0": "0",
    "MRR": "28.5",
    "RRX": "40.8",
    "D_RRX": "18/2004",
    "MNDRR01": "",
    "MNDRR1": "3.1",
    "MNDRR10": "1.1",
    "MNDRR20": "0.4",
    "MNDRR30": "0.1"
  },
  {
    "month": "Set",
    "MTX": "23.8",
    "MTN": "13.2",
    "MTT": "18.5",
    "TXX": "37",
    "D_TXX": "5/2006",
    "TNN": "5.8",
    "D_TNN": "27/1993",
    "MNDTX35": "0.2",
    "MNDTX30": "3.7",
    "MNDTX25": "12.6",
    "MNDTN20": "1",
    "MNDTN0": "0",
    "MRR": "60.4",
    "RRX": "61",
    "D_RRX": "17/1993",
    "MNDRR01": "",
    "MNDRR1": "5.9",
    "MNDRR10": "1.8",
    "MNDRR20": "0.9",
    "MNDRR30": "0.4"
  },
  {
    "month": "Out",
    "MTX": "18.4",
    "MTN": "10.7",
    "MTT": "14.5",
    "TXX": "29.4",
    "D_TXX": "04/1997 01/2005",
    "TNN": "2.2",
    "D_TNN": "25/2003",
    "MNDTX35": "0",
    "MNDTX30": "0",
    "MNDTX25": "2.1",
    "MNDTN20": "0",
    "MNDTN0": "0",
    "MRR": "173.7",
    "RRX": "71.5",
    "D_RRX": "27/2004",
    "MNDRR01": "",
    "MNDRR1": "11.7",
    "MNDRR10": "5.6",
    "MNDRR20": "3.5",
    "MNDRR30": "1.8"
  },
  {
    "month": "Nov",
    "MTX": "13.3",
    "MTN": "6.7",
    "MTT": "10",
    "TXX": "23.4",
    "D_TXX": "2/1996",
    "TNN": "-0.9",
    "D_TNN": "27/2005",
    "MNDTX35": "0",
    "MNDTX30": "0",
    "MNDTX25": "0",
    "MNDTN20": "0",
    "MNDTN0": "0.4",
    "MRR": "161.8",
    "RRX": "82.8",
    "D_RRX": "24/2006",
    "MNDRR01": "",
    "MNDRR1": "11.6",
    "MNDRR10": "5.2",
    "MNDRR20": "2.9",
    "MNDRR30": "1.5"
  },
  {
    "month": "Dez",
    "MTX": "10.7",
    "MTN": "4.6",
    "MTT": "7.7",
    "TXX": "19",
    "D_TXX": "13/1994",
    "TNN": "-2.9",
    "D_TNN": "24/2001",
    "MNDTX35": "0",
    "MNDTX30": "0",
    "MNDTX25": "0",
    "MNDTN20": "0",
    "MNDTN0": "1.9",
    "MRR": "201",
    "RRX": "86.5",
    "D_RRX": "1/2000",
    "MNDRR01": "",
    "MNDRR1": "12.8",
    "MNDRR10": "6.4",
    "MNDRR20": "3.4",
    "MNDRR30": "2"
  }
]

I am trying to filter out the data which key MTN has values greater than "4.5".
I'm using the following filter:

jq '.[] | if .MTN > "4.5" then .month else empty end' < Downloads/amCharts.json
"Mar"
"Abr"
"Mai"
"Nov"
"Dez"

But the result is not correct. All months since Mar until Dez have higher value for key MTN.
What am I doing wrong?

@psysc0rpi0n

if you only want the month

jq '.[] | select((.MTN | tonumber) > 4.5) | .month' psysc0rpi0n.json
"Mar"
"Abr"
"Mai"
"Jun"
"Jul"
"Ago"
"Set"
"Out"
"Nov"
"Dez"

#
# month and MTN 
#
jq -c '.[] | select((.MTN | tonumber) > 4.5) | {month, MTN}' psysc0rpi0n.json 
{"month":"Mar","MTN":"6.4"}
{"month":"Abr","MTN":"7.1"}
{"month":"Mai","MTN":"9.6"}
{"month":"Jun","MTN":"13"}
{"month":"Jul","MTN":"14.5"}
{"month":"Ago","MTN":"15.3"}
{"month":"Set","MTN":"13.2"}
{"month":"Out","MTN":"10.7"}
{"month":"Nov","MTN":"6.7"}
{"month":"Dez","MTN":"4.6"}


#
# if you want every element for each month matching (-c for compact output - demo purposes only , 
#
jq -c '.[] | select((.MTN | tonumber) > 4.5)' psysc0rpi0n.json 
{"month":"Mar","MTX":"15.5","MTN":"6.4","MTT":"11","TXX":"25.8","D_TXX":"22/2002","TNN":"-6.8","D_TNN":"1/2005","MNDTX35":"0","MNDTX30":"0","MNDTX25":"0.1","MNDTN20":"0","MNDTN0":"1.2","MRR":"98.2","RRX":"67.4","D_RRX":"2/2001","MNDRR01":"","MNDRR1":"8.3","MNDRR10":"3.5","MNDRR20":"1.5","MNDRR30":"0.7"}
{"month":"Abr","MTX":"16.4","MTN":"7.1","MTT":"11.8","TXX":"28.3","D_TXX":"30/1994","TNN":"-1.2","D_TNN":"20/1995","MNDTX35":"0","MNDTX30":"0","MNDTX25":"1.9","MNDTN20":"0","MNDTN0":"0.1","MRR":"118.7","RRX":"61.3","D_RRX":"8/2008","MNDRR01":"","MNDRR1":"11.1","MNDRR10":"4.1","MNDRR20":"1.5","MNDRR30":"0.6"}
{"month":"Mai","MTX":"19.4","MTN":"9.6","MTT":"14.5","TXX":"33.2","D_TXX":"29/2001","TNN":"1.8","D_TNN":"1/2001","MNDTX35":"0","MNDTX30":"0.7","MNDTX25":"5.1","MNDTN20":"0.4","MNDTN0":"0","MRR":"98.2","RRX":"74.6","D_RRX":"18/1996","MNDRR01":"","MNDRR1":"10.4","MNDRR10":"3.6","MNDRR20":"1","MNDRR30":"0.3"}
{"month":"Jun","MTX":"24.4","MTN":"13","MTT":"18.7","TXX":"35.4","D_TXX":"21/2001","TNN":"5.6","D_TNN":"9/1992","MNDTX35":"0.2","MNDTX30":"4.6","MNDTX25":"14.5","MNDTN20":"1.1","MNDTN0":"0","MRR":"37.6","RRX":"70","D_RRX":"1/1998","MNDRR01":"","MNDRR1":"4.6","MNDRR10":"0.8","MNDRR20":"0.5","MNDRR30":"0.3"}
{"month":"Jul","MTX":"27.6","MTN":"14.5","MTT":"21.1","TXX":"39.4","D_TXX":"24/1995","TNN":"7.3","D_TNN":"3/1997","MNDTX35":"1.9","MNDTX30":"10.6","MNDTX25":"21.9","MNDTN20":"3.4","MNDTN0":"0","MRR":"19.5","RRX":"38.4","D_RRX":"25/2000","MNDRR01":"","MNDRR1":"2.6","MNDRR10":"0.7","MNDRR20":"0.1","MNDRR30":"0.1"}
{"month":"Ago","MTX":"28","MTN":"15.3","MTT":"21.6","TXX":"38.6","D_TXX":"8/2003","TNN":"7.6","D_TNN":"25/1993","MNDTX35":"2.2","MNDTX30":"11.5","MNDTX25":"22.8","MNDTN20":"4.2","MNDTN0":"0","MRR":"28.5","RRX":"40.8","D_RRX":"18/2004","MNDRR01":"","MNDRR1":"3.1","MNDRR10":"1.1","MNDRR20":"0.4","MNDRR30":"0.1"}
{"month":"Set","MTX":"23.8","MTN":"13.2","MTT":"18.5","TXX":"37","D_TXX":"5/2006","TNN":"5.8","D_TNN":"27/1993","MNDTX35":"0.2","MNDTX30":"3.7","MNDTX25":"12.6","MNDTN20":"1","MNDTN0":"0","MRR":"60.4","RRX":"61","D_RRX":"17/1993","MNDRR01":"","MNDRR1":"5.9","MNDRR10":"1.8","MNDRR20":"0.9","MNDRR30":"0.4"}
{"month":"Out","MTX":"18.4","MTN":"10.7","MTT":"14.5","TXX":"29.4","D_TXX":"04/1997 01/2005","TNN":"2.2","D_TNN":"25/2003","MNDTX35":"0","MNDTX30":"0","MNDTX25":"2.1","MNDTN20":"0","MNDTN0":"0","MRR":"173.7","RRX":"71.5","D_RRX":"27/2004","MNDRR01":"","MNDRR1":"11.7","MNDRR10":"5.6","MNDRR20":"3.5","MNDRR30":"1.8"}
{"month":"Nov","MTX":"13.3","MTN":"6.7","MTT":"10","TXX":"23.4","D_TXX":"2/1996","TNN":"-0.9","D_TNN":"27/2005","MNDTX35":"0","MNDTX30":"0","MNDTX25":"0","MNDTN20":"0","MNDTN0":"0.4","MRR":"161.8","RRX":"82.8","D_RRX":"24/2006","MNDRR01":"","MNDRR1":"11.6","MNDRR10":"5.2","MNDRR20":"2.9","MNDRR30":"1.5"}
{"month":"Dez","MTX":"10.7","MTN":"4.6","MTT":"7.7","TXX":"19","D_TXX":"13/1994","TNN":"-2.9","D_TNN":"24/2001","MNDTX35":"0","MNDTX30":"0","MNDTX25":"0","MNDTN20":"0","MNDTN0":"1.9","MRR":"201","RRX":"86.5","D_RRX":"1/2000","MNDRR01":"","MNDRR1":"12.8","MNDRR10":"6.4","MNDRR20":"3.4","MNDRR30":"2"}

Need numeric testing

# .MTN in output only for testing - easier to check
# using select example 
jq  '.[] |  select(.MTN|tonumber > 4.5) | [.month,.MTN] | join(",")'  Downloads/amCharts.json
# or if testing
jq  '.[] |  if .MTN|tonumber > 4.5 then .month else empty end ' Downloads/amCharts.json

But why my command din't work as expected? Wy it missed quite a few months?

Because you are doing a character (string) match. That is what tonumber changes.

Look at Jul. 14.5 > 4.5, but "14.5" < 4.5 because as text, "1" < "4".

as @Paul_Pedant mentions ... string (lexical) comparisons don't go well when comparing numbers

#
# note the result of where the string "4.5" appears in this output - directly before the months
# returned from your initial attempt. ( the same 5 months !) 
# 
sort -k 2 nums
"MTN": "10.7"  , "month": "Out",
"MTN": "13"    , "month": "Jun",
"MTN": "13.2"  , "month": "Set",
"MTN": "14.5"  , "month": "Jul",
"MTN": "15.3"  , "month": "Ago",
"MTN": "3.8"   , "month": "Jan",
"MTN": "4.5"   , "month": "Fev",
"MTN": "4.6"   , "month": "Dez",
"MTN": "6.4"   , "month": "Mar",
"MTN": "6.7"   , "month": "Nov",
"MTN": "7.1"   , "month": "Abr",
"MTN": "9.6"   , "month": "Mai",

jq '.[] | select((.MTN)> "4.5") | .month' psysc0rpi0n.json
"Mar"
"Abr"
"Mai"
"Nov"
"Dez"

@psysc0rpi0n, glad the team could help you out !, tell all your friends to visit

My final goal was this:

 jq -r '.[] | select((.MTN | tonumber) > 4.5) | .MTN' < Downloads/amCharts.json

and

jq -r '.[] | select((.MTX | tonumber) > 4.5) | .MTN' < Downloads/amCharts.json | awk '{ total += $1; count++ } END { print total/count }'

to calculate the average values of those 2 keys, MTN and MTX.
I know I tarted with different goal, but I was just experimenting to try to get to this point!

Thank you all

@, you should ideally have started a new thread ... but since we're here.

• can you show what the result would be (obviously that'll need to be manually calculated for this), this is to minimise misunderstanding/ambiguities on our part . hopefully this will not take too long

thks

do you mean

.MTX | tonumber) > 4.5) | **.MTN**'

or 

.MTX | tonumber) > 4.5) | **MTX** 
?

Actually, I made a mistake. Thank you again for pointing me that.
I wanted to calculate the average value of all values in keys MTN and MTX.

So, it should rather be:

jq -r '.[] | select((.MTN | tonumber)) | .MTN' < Downloads/amCharts.json | awk '{ total += $1; count++ } END { print total/count }'
9.11667 

and

$ jq -r '.[] | select((.MTX | tonumber)) | .MTX' < Downloads/amCharts.json | awk '{ total += $1; count++ } END { print total/count }'
18.35

I still haven't checked the averages values by hand!

I'm no jq savvy, but I think you should be able to do your avg calculation within jq itself.
Googling would probably help how to do basic math within jq...

jq '[ .[] | select((.MTN | tonumber)) ] | map({ MTN: .MTN | tonumber }) | reduce .[] as $avg (0; . + $avg.MTN) / length' psysc0rpi0n.json

... ditto for the .MTX field

here's a bunch of urls that are useful

jq homepage

next few are sections from the previous link
jq cookbook
manual
language structure
tutorial
jq from the command line

why (it might seem to be) jq is a PIA to use

functional programming 'works' quite differently to most others !