JSONata_reference.MD

Count distinct aggregate multiple repeating elements

{"payload":[
  {
    "count": "13",
    "name": "joep",
    "total": 1
  },
  {
    "count": "7",
    "name": "joep",
    "total": 1
  },
  {
    "count": "3",
    "name": "kees",
    "total": 1
  },
  {
    "count": "2",
    "name": "joep",
    "total": 1
  },
  {
    "count": "119",
    "name": "bart",
    "total": 1
  },
  {
    "count": "251",
    "name": "joep",
    "total": 1
  },
  {
    "count": "0",
    "name": "bart",
    "total": 6
  }
]}

jsonata

$distinct(payload.name).(
    $Cur:= $;
    {
        "name":$,
        "count":$sum($$.payload[name=$Cur].count.$number()),
        "total":$sum($$.payload[name=$Cur].total)
})

result

[
  {
    "name": "joep",
    "count": 273,
    "total": 4
  },
  {
    "name": "kees",
    "count": 3,
    "total": 1
  },
  {
    "name": "bart",
    "count": 119,
    "total": 7
  }
]

Count distinct aggregate

{
  "names": [
    {
      "name": "joep"
    },
    {
      "name": "joep"
    },
    {
      "name": "kees"
    }
  ]
}

jsonata:

{
    'names': $.names{name: $count(name)} 
    ~> $each(function($v,$k){
        {
            'name': $k,
            'total': $v}
    })
}

result:

{
  "names": [
    {
      "name": "joep",
      "total": 2
    },
    {
      "name": "kees",
      "total": 1
    }
  ]
}

Array of objects, get average depth, where depth > 0

[
  {
    "Average Depth": 9
  },
    {
    "Average Depth": 0
  },
  {
    "Average Depth": 7
  }
 ]

jsonata:

$average(`Average Depth`[$>0])

result

8

Formatting output while averaging

• Input - array of measurements

{"payload":[{"measures": {
      "02:00:00:14:48:e6": {
        "res": {
          "1594359105": [
            14.8,
            96
          ]
        },
        "type": [
          "temperature",
          "humidity"
        ]
      },
      "70:ee:50:14:54:8a": {
        "res": {
          "1594359112": [
            1009.4
          ]
        },
        "type": [
          "pressure"
        ]
      }}
}]}

jsonata:

(payload.measures.*.$zip(type, res.*)).{
    'label': $[0],
    'value': $[1]
}{
    label: $average(value)
}

result

{
"temperature":14.8,
"humidity":96,
"pressure":1009.4
}

Get highest and lowest

input

[
  {
    "time": "2020-05-18T19:09:53.000Z",
    "Temperatur": 24.1
  },
  {
    "time": "2020-05-18T19:09:58.000Z",
    "Temperatur": 24
  },
  {
    "time": "2020-05-18T19:12:48.000Z",
    "Temperatur": 23.9
  },
  {
    "time": "2020-05-18T19:13:27.000Z",
    "Temperatur": 24
  }
]

jsonata (sort from high to low and get first+last):

$^(Temperatur)[[0, -1]]

result

[
  {
    "time": "2020-05-18T19:12:48.000Z",
    "Temperatur": 23.9
  },
  {
    "time": "2020-05-18T19:09:53.000Z",
    "Temperatur": 24.1
  }
]

Get distinct values, only non-empty objects

input

{
  "payload": [
    {
      "Country": "Netherlands Antilles"
    },
    {
      "Country": "United States"
    },
    {
      "Country": "United States"
    },
    {
      "Country": "Bahamas"
    },
    {
      "Country": ""
    },
    {
      "Country": ""
    }
  ]
}

jsonata

$distinct(payload[Country]).{
    'country': Country
}

result

[
  {
    "country": "Netherlands Antilles"
  },
  {
    "country": "United States"
  },
  {
    "country": "Bahamas"
  }
]