How to select max value by condition and then compare it with others?

Question

I have a backup set that is described by json. Sample is below. I want to count how much increment backups were added since the last full backup. I try to select max timestamp of the record with type "full" so after that i will count how much records with type "incr" has the bigger timestamp.

{
  "archive": [
    {
      "database": {
        "id": 1
      },
      "id": "11-1",
      "max": "0000000A000018B90000006A",
      "min": "0000000A0000167D000000C7"
    }
  ],
  "backup": [
    {
      "archive": {
        "start": "0000000A0000181600000030",
        "stop": "0000000A0000181C00000083"
      },
      "backrest": {
        "format": 5,
        "version": "2.28"
      },
      "database": {
        "id": 1
      },
      "info": {
        "delta": 417875448942,
        "repository": {
          "delta": 67466720725,
          "size": 67466720725
        },
        "size": 417875448942
      },
      "label": "20201213-200009F",
      "prior": null,
      "reference": null,
      "timestamp": {
        "start": 1607878809,
        "stop": 1607896232
      },
      "type": "full"
    },
    {
      "archive": {
        "start": "0000000A0000182900000065",
        "stop": "0000000A0000182F00000069"
      },
      "backrest": {
        "format": 5,
        "version": "2.28"
      },
      "database": {
        "id": 1
      },
      "info": {
        "delta": 122520170241,
        "repository": {
          "delta": 19316550760,
          "size": 67786280115
        },
        "size": 416998156028
      },
      "label": "20201213-200009F_20201214-200009I",
      "prior": "20201213-200009F",
      "reference": [
        "20201213-200009F"
      ],
      "timestamp": {
        "start": 1607965209,
        "stop": 1607974161
      },
      "type": "incr"
    },
    {
      "archive": {
        "start": "0000000A0000185B000000DD",
        "stop": "0000000A0000185B000000F4"
      },
      "backrest": {
        "format": 5,
        "version": "2.28"
      },
      "database": {
        "id": 1
      },
      "info": {
        "delta": 126982395984,
        "repository": {
          "delta": 19541379733,
          "size": 67993072945
        },
        "size": 421395153101
      },
      "label": "20201213-200009F_20201217-200105I",
      "prior": "20201213-200009F_20201214-200009I",
      "reference": [
        "20201213-200009F",
        "20201213-200009F_20201214-200009I"
      ],
      "timestamp": {
        "start": 1608224465,
        "stop": 1608233408
      },
      "type": "incr"
    }
  ]
}

I tried to complete first part by this command but it says that "number (1607896232) and number (1607896232) cannot be iterated over"

.[0] |.backup[] | select(.type=="full").timestamp.stop|max

I tried sort_by but has no luck. So what am I doing wrong here?

score 1 · Answer 1 · answered Dec 21 '20 at 13:52

1

max expects an array.

[ .backup[] | select( .type == "full" ).timestamp.stop ] | max

Test

or

.backup | map( select( .type == "full" ).timestamp.stop ) | max

Test

answered Dec 21 '20 at 13:52

ikegami

367,544
15
269
518

peak · Accepted Answer · 2020-12-21T16:12:29.040

With the aid of a generic helper-function for counting, here's a complete solution, assuming you want to count based on .timestamp.start:

def count(s): reduce s as $x (0; .+1);

.backup
| (map( select( .type == "full" ).timestamp.stop) | max) as $max
| count(.[] | select( .type == "incr" and  .timestamp.start > $max))

Using `max/1`

For large arrays, it would probably be more efficient to use a streaming version of max:

def count(s): reduce s as $x (0; .+1);

# Note: max(empty) #=> null
def max(s):
  reduce s as $s (null; if $s > .m then $s else . end);

.backup
| max(.[] | select( .type == "full" ).timestamp.stop) as $max
| count(.[] | select( .type == "incr" and  .timestamp.start > $max))

score 0 · Answer 3 · answered Dec 22 '20 at 13:49

So, after I solved problem with getting concrete value (thanks @ikegami), I solved my entire question by this way

 jq '(.[0] |[.backup[] | select(.type=="full").timestamp.stop]|max) as $i| [.[0] |.backup[] | select(.type=="incr" and .timestamp.stop>$i)]|length

Not sure if it made optimal, but it works anyway.

Dmitry · Answer 4 · 2020-12-23T16:01:16.173

0

Here's also an alternative (non-jq) solution how to achieve the same JSON query with jtc tool:

bash $ <input.json jtc -jw'[timestamp]:<>G:[-1][type]' / -w'<full><>k'
2

PS. I'm a developer of jtc unix JSON processor
PPS. the above disclaimer is required by SO.

edited Dec 23 '20 at 16:01

answered Dec 23 '20 at 15:40

Dmitry

1,275
1
5
14

How to select max value by condition and then compare it with others?

4 Answers4

Using max/1

Using `max/1`