CONVERT ACCUMULATIVE DATA TO REAL TIME DATA USE CASE EXAMPLE

[gdiana]

Quite often many electrical meters of energy systems provide accumulative data which just shows a straight incremental line, when one desires to see the actual load profile or differential / incremental energy usage per sample time.

Many low cost meters do not provide incremental data but only accumulative data and then one has to try and use fancy queries to try and get the actual load profile etc using say Modbus or MQTT.

One may do this in ones database query but if JSON data is available then one may use the JSONATA parser provided in the INFINITY DATA SOURCE. This is far more powerful, easier and flexible to do rather than trying to do this within UQL.

So this is what we get using accumulative data

and by using JSONATA we get

This may be achieved by using the following JSONATA script on the following json data structure

[
  {
    "Timestamp": "1749610800",
    "PhaseA": "15956.4",
    "PhaseB": "24443.1",
    "PhaseC": "34066.9",
    "System": "74469.1"
  },
  {
    "Timestamp": "1749614400",
    "PhaseA": "15961.5",
    "PhaseB": "24447.8",
    "PhaseC": "34069.7",
    "System": "74481.7"
  },
  {
    "Timestamp": "1749618000",
    "PhaseA": "15968.8",
    "PhaseB": "24453.6",
    "PhaseC": "34078.2",
    "System": "74503.2"
  },
  {
    "Timestamp": "1749621600",
    "PhaseA": "15979.4",
    "PhaseB": "24457.8",
    "PhaseC": "34083.6",
    "System": "74523.5"
  },
  {
    "Timestamp": "1749625200",
    "PhaseA": "15987.7",
    "PhaseB": "24461.1",
    "PhaseC": "34090.1",
    "System": "74541.6"
  },
  {
    "Timestamp": "1749628800",
    "PhaseA": "15994.4",
    "PhaseB": "24464.9",
    "PhaseC": "34094.7",
    "System": "74556.7"
  }
]

The JSONATA map function allows one to parse the json data ($v) with index ($i) and a static copy of the json data ($a).

Using the copy one can access the differential data, but which requires one to retrieve energy data one sample earlier and one sample later for any desired time period. ie required time period ie t(n-1) ----- t(N+1) opposed to t(n)--- T(N). If the sample period is say 30min then it must start one sample before the start time and end one sample after.

The $ is the Json path to the energy usage data. In this case it is just $ as it is at the top

$map($,function($v,$i,$a){{
   "timestamp"  : $a[$i+1].$fromMillis($number(Timestamp)*1000),
   "Ptot"       : ($number($a[$i+1].`System`)   - $number($a[$i].System) ),
   "Pa"         : ($number($a[$i+1].`PhaseA`) - $number($a[$i].`PhaseA`) ),
   "Pb"         : ($number($a[$i+1].`PhaseB`)  - $number($a[$i].`PhaseB`) ),
   "Pc"         : ($number($a[$i+1].`PhaseC`) - $number($a[$i].`PhaseC`) )
}})

If one tries to do the above using $v instead of $a one gets an error as the $v data is changed or dynamic whereas its copy $a remains static, thereby allowing one to subtract the next sample from the previous sample and store the difference in $v.

Note I have used Phase' as this must be used when any variable has a space in its name such as Phase A

The result is that one now gets graphs which are far more meaningful.

Hope this helps a little.

[yesoreyeram]

Good one @gdiana. Here is an another version of JSONata if anyone interested with the consideration of dynamic keys

$map($, function($v, $index, $vv){    
        $merge([
            $map($keys($vv),function($key){
                { 
                    $key : 
                        $key = "Timestamp" ? 
                        $number($lookup($vv[$index],$key)) : 
                        $index = 0 ? null : $number($lookup($vv[$index],$key)) - $number($lookup($vv[$index -1],$key))
                }
            })
        ])
})

[gdiana]

He, he just when I thought I was being clever ! 😂😂😂

But yes finding ways to create new columns of data which are some function etc of the original data is very very useful and powerful especially when one wishes to create features such as data drill down.