Reporting & analytics with jq

The Scripting guide lists starter jq patterns. This recipe expands them into a small analytics toolkit you can paste into a weekly report job.

All recipes assume:

WORKSPACE=myworkspace
REPO=myrepo

Two things will bite you on this page if you skip them:

• count is what was fetched, not what exists. bb pr list stops at --limit 25 by default, so .count maxes out at 25 and the Use --limit <n> or --all to see more. hint is suppressed under --json. Pass --all whenever the number itself is the answer.
• --jq JSON-encodes its output. There is no raw mode. When you need a bare string for the shell or a file, pipe --json into external jq -r instead.

Count pull requests per state

bb pr list returns one state at a time, so fetch each separately and combine:

for state in OPEN MERGED DECLINED SUPERSEDED; do
  count=$(bb pr list -w "$WORKSPACE" -r "$REPO" -s "$state" --all --json --jq '.count')
  printf "%-12s %s\n" "$state" "$count"
done

Sample output:

OPEN         12
MERGED       340
DECLINED     8
SUPERSEDED   2

The loop is not avoidable: --state takes one value and defaults to OPEN, so a single fetch can only ever contain one state and group_by(.state) would return one group. Grouping is still useful on other attributes of a single fetch — open PRs per target branch, for example:

bb pr list -w "$WORKSPACE" -r "$REPO" --all --json --jq '
  .pullRequests
  | group_by(.destination.branch.name)
  | map({ branch: .[0].destination.branch.name, count: length })
  | sort_by(-.count)
'

Sum additions and deletions across PRs

bb pr list does not include diff stats. bb pr diff <id> --stat --json does, and it already totals them for you:

{
  "workspace": "myworkspace",
  "repoSlug": "myrepo",
  "pullRequestId": 42,
  "mode": "stat",
  "files": [{ "path": "src/auth.ts", "additions": 31, "deletions": 4 }],
  "filesChanged": 1,
  "totalAdditions": 31,
  "totalDeletions": 4
}

The totals are per PR — there is no aggregate endpoint — so the outer loop still stands. Read totalAdditions and totalDeletions rather than re-summing .files:

#!/bin/bash
total_added=0
total_deleted=0

for pr_id in $(bb pr list -w "$WORKSPACE" -r "$REPO" -s MERGED --all --json \
                 --jq '.pullRequests[].id'); do
  stats=$(bb pr diff "$pr_id" -w "$WORKSPACE" -r "$REPO" --stat --json)
  added=$(echo "$stats" | jq '.totalAdditions')
  deleted=$(echo "$stats" | jq '.totalDeletions')
  total_added=$(( total_added + added ))
  total_deleted=$(( total_deleted + deleted ))
  sleep 1
done

echo "Total added: $total_added"
echo "Total deleted: $total_deleted"

Tip

This makes one HTTP request per PR. For a few dozen PRs it’s fine; for hundreds, schedule it overnight or scope to a date range with --jq '... | select(.updated_on >= "2025-01-01")' before the inner loop. That is a lexicographic string compare, which works because Bitbucket’s timestamps are zero-padded ISO 8601.

Group by author with counts

bb pr list -w "$WORKSPACE" -r "$REPO" --all --json --jq '
  .pullRequests
  | group_by(.author.nickname // .author.display_name)
  | map({
      author: (.[0].author.nickname // .[0].author.display_name),
      count: length,
      ids: [.[].id]
    })
  | sort_by(-.count)
'

This sorts authors by descending PR count and includes the IDs for drilldown.

Top reviewers across merged PRs

Useful for identifying review load. Iterates over merged PRs and counts approvals per reviewer:

bb pr list -w "$WORKSPACE" -r "$REPO" -s MERGED --limit 200 --json --jq '
  [
    .pullRequests[].participants[]
    | select(.approved == true)
    | .user.nickname // .user.display_name
  ]
  | group_by(.)
  | map({ reviewer: .[0], approvals: length })
  | sort_by(-.approvals)
'

CSV export

Use jq’s @csv — through external jq -r, not --jq. @csv produces a jq string, and --jq would JSON-encode it again, wrapping every line in a second pair of quotes and backslash-escaping the inner ones. The result is not valid CSV.

bb pr list -w "$WORKSPACE" -r "$REPO" --all --json | jq -r '
  ["id","title","author","state","created_on","source","destination"],
  (
    .pullRequests[]
    | [
        .id,
        .title,
        (.author.nickname // .author.display_name),
        .state,
        .created_on,
        .source.branch.name,
        .destination.branch.name
      ]
  )
  | @csv
' > prs.csv

head -3 prs.csv

Sample output:

"id","title","author","state","created_on","source","destination"
42,"Add login button","alice","OPEN","2025-01-04T09:11:00.993890+00:00","feat/login","main"
43,"Fix typo in README","bob","OPEN","2025-01-04T11:22:41.118201+00:00","fix/typo","main"

PR cycle time (created → merged)

Bitbucket timestamps look like 2018-08-15T23:50:59.993890+00:00. jq’s fromdateiso8601 is strptime("%Y-%m-%dT%H:%M:%SZ"), which rejects both the fractional seconds and the numeric offset — feeding it a raw Bitbucket timestamp fails the whole filter with jq evaluation failed: … (error code 8001), it does not just skip a row. Strip both first:

bb pr list -w "$WORKSPACE" -r "$REPO" -s MERGED --limit 100 --json --jq '
  def ts: sub("\\.[0-9]+"; "") | sub("\\+00:00"; "Z") | fromdateiso8601;
  [
    .pullRequests[]
    | {
        id,
        title,
        hours: ((.updated_on | ts) - (.created_on | ts)) / 3600
      }
  ]
  | sort_by(.hours)
'

The sub("\\.[0-9]+"; "") is a no-op on the fraction-less variant Bitbucket also emits, so ts handles both.

updated_on on a merged PR approximates the merge time. For the exact timestamp, read the merge event:

bb pr activity 42 -w "$WORKSPACE" -r "$REPO" --type merge --json --jq '.activities[0].merge.date'

Related

• Scripting & Automation guide — JSON output, exit codes, primitive jq patterns
• JSON Output reference — the envelope shape for list-style commands
• bb pr list — flags like -s, --limit, --json, --jq