CODEWITHSUNDEEP
jsonjq
user2556342
user2556342

Reputation: 55

jq- Periodically add calculated values, based on exisiting values, to .json file

There is an input file which contains several objects connected to one specific day. (In this case it's 4th of October). Each Object however, represents another time of that day, with different VALUES. "ID" and day are always the same in the file:

INPUT.json:

[
  {
    "DATE": "04.10.2017 10:20",
    "ID":"x",
    "VALUE_ONE": 20,
    "VALUE_TWO": 3
  },
  {
    "DATE": "04.10.2017 12:50",
    "ID":"x",
    "VALUE_ONE": 40,
    "VALUE_TWO": 5
  },
  {
    "DATE": "04.10.2017 14:20",
    "ID":"x",
    "VALUE_ONE": 10,
    "VALUE_TWO": 2
  }   
]

What I want to do now is calculate the day's total values and also do some math. (in this case either add or multiply by 3).

The results shall be added to the 'consolidated.json' file, which should contain one object for each day. To ensure that, this process will be repeated daily.

I have commented the calculation steps behind '//' :

CONSOLIDATED.json:

[
  {
    "DATE": "02.10.2017",
    "VALUE_ONE_TODAY": 40,
    "VALUE_ONE_TOTAL": 800,
    "VALUE_THREE_TODAY": 5,
    "VALUE_THREE_TOTAL": 110
  },
  {
    "DATE": "03.10.2017",
    "VALUE_ONE_TODAY": 90,
    "VALUE_ONE_TOTAL": 890, // =800+90
    "VALUE_THREE_TODAY": 8,
    "VALUE_THREE_TOTAL": 134 // = 110 + 3*8
  },
  {           //this object is new!
    "DATE": "04.10.2017",
    "VALUE_ONE_TODAY": 70,
    "VALUE_ONE_TOTAL": 960, // =890+70
    "VALUE_THREE_TODAY": 10,
    "VALUE_THREE_TOTAL": 164 // =134 +3*10
  }
]

I know I can get the totals of INPUT.json with 

[.[]|.VALUE_ONE]|add

and

[.[]|.VALUE_TWO*3]|add

But I am not sure how to get this into the desired structure and use last day's totals. Thanks!

Upvotes: 0

Views: 1276

Answers (2)

peak
peak

Reputation: 116919

Here is a straightforward solution, which for simplicity of exposition makes the following (easy-to-work-around) assumptions:

  • The contents of input.json are as advertised, and in particular, that the date of objects in input.json does not yet occur in consolidated.json;
  • The last entry in the array in consolidated.json has the most recent date, and that it is prior to the date of the entries in input.json

consolidate.jq

def aggregate:
  {DATE: (.[0]|.DATE|split(" ")[0]),
   VALUE_ONE_TODAY: (map(.VALUE_ONE) | add),
   VALUE_TWO_TODAY: (map(.VALUE_TWO) | add)}
  ;

.[-1] as $previous
| ($i | aggregate
   | {DATE,
      VALUE_ONE_TODAY,
      VALUE_ONE_TOTAL : ($previous.VALUE_ONE_TOTAL + .VALUE_ONE_TODAY),
      VALUE_THREE_TODAY : ($previous.VALUE_TWO_TOTAL + .VALUE_TWO_TODAY) }
   |  .VALUE_THREE_TOTAL = ($previous.VALUE_THREE_TOTAL + 3 * .VALUE_THREE_TODAY) ) as $today
| . + [$today]

Invocation

jq -f consolidate.jq --argfile i input.json consolidated.json

If you are sure it is safe to do so, you could tack on: | sponge consolidated.json

Output

The added entry is as requested, namely:

{
  "DATE": "04.10.2017",
  "VALUE_ONE_TODAY": 70,
  "VALUE_ONE_TOTAL": 960,
  "VALUE_THREE_TODAY": 10,
  "VALUE_THREE_TOTAL": 164
}

Upvotes: 0

jq170727
jq170727

Reputation: 14705

Here is a solution:

#!/bin/bash

jq -M --argfile i input.json '

    def VALUE_keys: keys[] | select(startswith("VALUE"));
    def TODAY_keys: keys[] | select(endswith("TODAY"));
    def TODAY_($k): "\($k)_TODAY";
    def TOTAL_($k): "\($k)_TOTAL";

    def new_totals: 
        $i
      | (map(VALUE_keys)|unique) as $attrs
      | reduce (.[]|.DATE |= .[:10]) as $d ({}
        ; reduce $attrs[] as $a (.
          ; [$d.DATE, $a] as $p | setpath($p; getpath($p)+$d[$a])
          )
        )
      | keys[] as $date 
      | [$date, .[$date]] ;

    def aggregate($k; $total):
        .[TODAY_($k)] = $total
      | if $k == "VALUE_THREE" 
        then .[TOTAL_($k)] += $total * 3
        else .[TOTAL_($k)] += $total
        end ;

    def next_day:
        foreach new_totals as [$date, $totals] (
           max_by(.DATE) 
         ; if .DATE == $date then empty else . end
         | .DATE = $date
         | .[TODAY_keys] = 0 
         | reduce ($totals|keys[]) as $k (.; aggregate($k; $totals[$k]))
       )
    ;

    . + [next_day]


' consolidated.json | sponge consolidated.json

new_totals computes the input.json sums returning the date and an array of values. From the sample data it produces:

[
  "04.10.2017",
  {
    "VALUE_ONE": 70,
    "VALUE_TWO": 10
  }
]

next_day applies these values to the latest entry of consolidated.json returning a value for the next day. E.g.

{
  "DATE": "04.10.2017",
  "VALUE_ONE_TODAY": 70,
  "VALUE_ONE_TOTAL": 960,
  "VALUE_THREE_TODAY": 0,
  "VALUE_THREE_TOTAL": 134,
  "VALUE_TWO_TODAY": 10,
  "VALUE_TWO_TOTAL": 10
}

Note that the example output is inconsistent with the sample input.json so the values differ.

The script adds this to the input and uses sponge to update consolidated.json with the new data. Note that the line

| if .DATE == $date then empty else . end

prevents it from adding the data for a new day if that day is already present. This is unnecessary if other safeguards are in place.

Note also that next_day uses foreach to handle the situation where input.json contains data for more then one day. For example if the first element of input.json were

{
   "DATE": "03.12.2017 10:20",
   "ID":"x",
   "VALUE_ONE": 20,
   "VALUE_TWO": 3
}

next_day would generate two entries:

{
  "DATE": "03.12.2017",
  "VALUE_ONE_TODAY": 20,
  "VALUE_ONE_TOTAL": 910,
  "VALUE_THREE_TODAY": 0,
  "VALUE_THREE_TOTAL": 134,
  "VALUE_TWO_TODAY": 3,
  "VALUE_TWO_TOTAL": 3
}
{
  "DATE": "04.10.2017",
  "VALUE_ONE_TODAY": 50,
  "VALUE_ONE_TOTAL": 960,
  "VALUE_THREE_TODAY": 0,
  "VALUE_THREE_TOTAL": 134,
  "VALUE_TWO_TODAY": 7,
  "VALUE_TWO_TOTAL": 10
}

Try it online!

Upvotes: 1

Related Questions