如何使用JQ将ElasticSearch JSON输出为缺失值补0的表格

Got it, let's work through this. You've already got a line-by-line output (asset + date + value) from jq + sed, and now you want to pivot that into a clean table where dates are columns, assets are rows, and missing date-asset pairs get filled with 0. I'll cover two approaches: directly generating the table from your ElasticSearch aggregation response (more efficient, no sed needed), and post-processing your existing line output.

Approach 1: Directly from ElasticSearch Aggregation Response #

First, let's assume your ES aggregation response looks something like this (adjust field names if yours differ):

{
  "aggregations": {
    "assets": {
      "buckets": [
        {
          "key": "asset_001",
          "date_buckets": {
            "buckets": [
              {
                "key_as_string": "2024-05-01",
                "totalMaxUptime": { "value": 100 }
              },
              {
                "key_as_string": "2024-05-03",
                "totalMaxUptime": { "value": 95 }
              }
            ]
          }
        },
        {
          "key": "asset_002",
          "date_buckets": {
            "buckets": [
              {
                "key_as_string": "2024-05-01",
                "totalMaxUptime": { "value": 98 }
              },
              {
                "key_as_string": "2024-05-02",
                "totalMaxUptime": { "value": 100 }
              }
            ]
          }
        }
      ]
    }
  }
}

Use this jq command to generate a Markdown table directly:

(
  # Collect and sort all unique dates
  [.aggregations.assets.buckets[].date_buckets.buckets[].key_as_string] | unique | sort as $dates
)
(
  # Collect all unique assets
  [.aggregations.assets.buckets[].key] | unique as $assets
)
# Build a map for each asset: date -> value (0 if missing)
| .aggregations.assets.buckets | map(
  .key as $asset
  | reduce .date_buckets.buckets[] as $b ({}; .[$b.key_as_string] = $b.totalMaxUptime.value)
  | { asset: $asset, values: $dates | map(.[.] // 0) }
) as $rows
# Generate Markdown table structure
| "| Asset ID |" + ($dates | map(" " + . + " |") | join(""))
, "|-----------|" + ($dates | map("---------|") | join(""))
, ($rows | map("| " + .asset + " |" + (.values | map(" " + tostring + " |") | join(""))) | .[])

If you prefer CSV instead (easier for spreadsheets), use this variant:

(
  [.aggregations.assets.buckets[].date_buckets.buckets[].key_as_string] | unique | sort as $dates
)
(
  [.aggregations.assets.buckets[].key] | unique as $assets
)
| .aggregations.assets.buckets | map(
  .key as $asset
  | reduce .date_buckets.buckets[] as $b ({}; .[$b.key_as_string] = $b.totalMaxUptime.value)
  | { asset: $asset, values: $dates | map(.[.] // 0) }
) as $rows
| "Asset ID," + ($dates | join(","))
, ($rows | map(.asset + "," + (.values | join(","))) | .[])

Approach 2: Post-Process Your Existing Line Output #

If you already have a file (let's call it asset_data.txt) with lines like:

asset_001 2024-05-01 100
asset_001 2024-05-03 95
asset_002 2024-05-01 98
asset_002 2024-05-02 100

Use this awk script (pivot_to_table.awk) to convert it to a Markdown table:

BEGIN {
  # Read all data into a 2D map: asset -> date -> value
  while (getline < "asset_data.txt") {
    data[$1][$2] = $3
    all_dates[$2] = 1
    all_assets[$1] = 1
  }

  # Sort dates and assets alphabetically
  sort_array(all_dates, sorted_dates)
  sort_array(all_assets, sorted_assets)

  # Print Markdown header
  printf "| Asset ID |"
  for (i in sorted_dates) printf " %s |", sorted_dates[i]
  printf "\n|-----------|"
  for (i in sorted_dates) printf "---------|"
  printf "\n"

  # Print each asset's row
  for (a in sorted_assets) {
    asset = sorted_assets[a]
    printf "| %s |", asset
    for (d in sorted_dates) {
      date = sorted_dates[d]
      printf " %s |", data[asset][date] ? data[asset][date] : 0
    }
    printf "\n"
  }
}

# Helper function to sort an associative array into an indexed array
sort_array(input_arr, output_arr) {
  idx = 1
  for (item in input_arr) output_arr[idx++] = item
  n = idx - 1
  for (i=1; i<n; i++) {
    for (j=i+1; j<=n; j++) {
      if (output_arr[i] > output_arr[j]) {
        temp = output_arr[i]
        output_arr[i] = output_arr[j]
        output_arr[j] = temp
      }
    }
  }
}

Run it with:

awk -f pivot_to_table.awk

Both approaches will give you a table like this:

Just make sure to adjust field names (like date_buckets if your aggregation has a different name) to match your actual ES response.

内容的提问来源于stack exchange，提问作者am1991