Aggregate K8s Pod Counts by Namespace and Phase

Extracts all pods from a Kubernetes cluster and uses DuckDB to aggregate counts by namespace and phase.

$ kubectl get pods -A -o json | \
  jq -c '.items[]' | \
  duckdb -c "SELECT metadata.namespace, status.phase, count(*) as pod_count FROM read_json_auto('/dev/stdin') GROUP BY ALL ORDER BY pod_count DESC;"

1. [01] kubectlkubectl get pods -A -o json
   Fetches all pods across all namespaces and outputs the result as a single JSON object.
2. [02] jqjq -c '.items[]'
   Extracts the array of pod objects and streams them as newline-delimited JSON (NDJSON), which DuckDB can easily ingest.
3. [03] duckdbduckdb -c "SELECT ... FROM read_json_auto('/dev/stdin')"
   Reads the NDJSON stream from standard input directly into a DuckDB table, automatically inferring the schema.
4. [04] duckdbGROUP BY ALL ORDER BY pod_count DESC
   Aggregates the pod counts by namespace and phase, sorting the highest counts first.

┌─────────────┬─────────┬───────────┐
│  namespace  │  phase  │ pod_count │
│   varchar   │ varchar │   int64   │
├─────────────┼─────────┼───────────┤
│ kube-system │ Running │        12 │
│ default     │ Running │         5 │
│ monitoring  │ Running │         3 │
└─────────────┴─────────┴───────────┘

• Requires cluster-wide read permissions (get pods at the cluster scope).
• For very large clusters (10,000+ pods), the initial JSON payload might be large; ensure sufficient memory for jq to parse the initial array.