How I can round digit on the last column to 2 decimal after a dot using JQ?

Question

How I round digit on the last column to 2 decimal places?

I have json:

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 9,
    "successful": 9,
    "failed": 0
  },
  "hits": {
    "total": 2,
    "max_score": 2.575364,
    "hits": [
      {
        "_index": "my-2017-08",
        "_type": "log",
        "_id": "AV5V8l0oDDWj-VP3YnCw",
        "_score": 2.575364,
        "_source": {
          "acb": {
            "version": 1,
            "id": "7",
            "owner": "pc",
            "item": {
              "name": "Account Average Latency",
              "short_name": "Generate",
              "description": "Generate of last month"
            },
            "service": "gsm"
          },
          "@timestamp": "2017-07-31T22:00:00.000Z",
          "value": 210.08691986891395
        }
      },
      {
        "_index": "my-2017-08",
        "_type": "log",
        "_id": "AV5V8lbE28ShqBNuBl60",
        "_score": 2.575364,
        "_source": {
          "acb": {
            "version": 1,
            "id": "5",
            "owner": "pc",
            "item": {
              "name": "Profile Average Latency",
              "short_name": "Profile",
              "description": "Profile average latency of last month"
            },
            "service": "gsm"
          },
          "@timestamp": "2017-07-31T22:00:00.000Z",
          "value": 370.20963260148716
        }
      }
    ]
  }
}

I use JQ to get csv data:

["Name","Description","Result"],(.hits.hits[]._source | [.acb.item.name,.acb.item.description,.value])|@csv

I see result:

"Name","Description","Result"
"Account Average Latency","Generate of last month",210.08691986891395
"Profile Average Latency","Profile average latency of last month",370.20963260148716

I have 210.08691986891395 and 370.20963260148716 but I want 210.09 and 370.21

Jeff Mercado · Answer 1 · 2017-09-08T16:33:53.287

14

Depending on your build of jq, you may have access to some cstdlib math functions (e.g., sin or cos). Since you're on *nix, you very likely do. In my particular build, I don't seem to have access to round but perhaps you do.

def roundit: .*100.0|round/100.0;
["Name","Description","Result"],
(.hits.hits[]._source | [.acb.item.name, .acb.item.description, (.value|roundit)])
    | @csv

Fortunately, it could be implemented in terms of floor which I do have access to.

def roundit: .*100.0 + 0.5|floor/100.0;

edited Sep 08 '17 at 16:33

answered Sep 08 '17 at 15:34

Jeff Mercado

129,526
32
251
272

Thank for your answer. But I check this and I see an error: jq: error: round/0 is not defined at , line 1: def roundit: .*100.0 |round/100.0; ["Name","Description","Result"], (.hits.hits[]._source | [.acb.item.name, .acb.item.description, .value|roundit])| @csv jq: 1 compile error yes, I'm using linux (jq version 1.5) – Serg R Sep 08 '17 at 15:46
Yeah, it would seem that `round` isn't available for you either. But we could implement in terms of `floor` too. Not exactly the same but would be close enough. – Jeff Mercado Sep 08 '17 at 16:44
Per the official docs, it farms out the actual math work (including `round` and `floor`) to the OS. It's not your JQ build, but by design. I guess I never ran into this before because Bash has done what I needed until now. https://stedolan.github.io/jq/manual/#Math – Beweeted Jun 27 '22 at 04:01
@Beweeted The binary could have been built with or without math support, that's entirely possible. Though it's probably safe to assume that if you get it from official sources, it will have it. – Jeff Mercado Jun 28 '22 at 00:14
how can we check if the OS has the math lib and access to `round` or `floor`? – Tilo Dec 30 '22 at 18:48
1.6 seems to add feature here, this works for me: https://stackoverflow.com/a/59063724/1747983 – Tilo Dec 30 '22 at 18:58
1

@Tilo It depends on the build you get. If you're compiling from source, it's entirely dependent on what functions are available on the build machine. So try to find a built version that includes all. I think the official releases should have them. – Jeff Mercado Dec 30 '22 at 20:46

score 0 · Answer 2 · answered Sep 08 '17 at 17:14

Here is your current filter with minor reformatting:

  ["Name", "Description", "Result"]
, (   .hits.hits[]._source
    | [.acb.item.name, .acb.item.description, .value]
  )
| @csv

Here is a filter which rounds the value column. Note we do this after the @csv so that we have full control over the string

def round:                                                # e.g.
    (split(".") + ["0"])[:2]                              # ["210","08691986891395"]
  | "\(.[1])000"[:3] as $x | [.[0], $x[:2], $x[2:3]]      # ["210","08","6"]
  | map(tonumber)                                         # [210,8,6]
  | if .[2] >  4 then .[2] = 0 | .[1] += 1 else . end     # [210,9,0]
  | if .[1] > 99 then .[1] = 0 | .[0] += 1 else . end     # [210,9,0]
  | ["\(.[0])", "00\(.[1])"[-2:]]                         # ["210","09"]
  | join(".")                                             # 210.09
;

  (   ["Name", "Description", "Result"] | @csv )
, (   .hits.hits[]._source
    | [.acb.item.name, .acb.item.description, .value]
    | @csv
    | split(",") | .[-1] |= round | join(",")
  )

If this filter is in filter.jq and the sample data is in data.json then the command

$ jq -Mr -f filter.jq data.json

produces

"Name","Description","Result"
"Account Average Latency","Generate of last month",210.09
"Profile Average Latency","Profile average latency of last month",370.21

score -1 · Accepted Answer · answered Sep 08 '17 at 14:29

I would pass it to awk via pipeline:

jq -r '["Name","Description","Result"],(.hits.hits[]._source |
       [.acb.item.name,.acb.item.description,.value])|@csv' yourfile | 
       awk 'BEGIN{ FS=OFS="," }NR>1{ $3=sprintf("%.2f",$3) }1'

The output:

"Name","Description","Result"
"Account Average Latency","Generate of last month",210.09
"Profile Average Latency","Profile average latency of last month",370.21

How I can round digit on the last column to 2 decimal after a dot using JQ?

3 Answers3

Linked