Parsing deeply pipelined aggregations

README.md

Parsing deeply nested es queries into csvs with jq

If you already have data in ES, you can get a lot of data from queries but sometimes the complex return structure is annoying to deal with.

This example shows how to turn a complex query result into a csv.

• example_mapping_template.json shows the structure of the data stored in elasticsearch
• parsing_example.sh shows how to make a complex query against this data and parse the result into a csv
• example_output.csv shows sample csv output

example_mapping_template.json

{
    "template_messages": {
        "template": "*",
        "order": 0,
        "settings": {
            "index.mapping.ignore_malformed": 1
        },
        "mappings": {
            "Timestamp": {
                "type": "date"
            },
            "user_id": {
                "type": "string",
                "index": "not_analyzed"
            },
            "product_id": {
                "type": "string",
                "index": "not_analyzed"
            }
        }
    }
}

example_output.csv

parsing_example.sh

# Example triple nested aggregation
# Grabs count of products per user per time period
cat > requestbody.json << EOF
{
  "aggs": {
    "timestamp": {
      "aggs": {
        "user_id": {
          "aggs": {
            "metric": {
              "terms": {
                "field": "product_id"
              }
            }
          },
          "terms": {
            "field": "user_id"
          }
        }
      },
      "date_histogram": {
        "field": "Timestamp",
        "interval": "12h"
      }
    }
  },
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "Timestamp": {
              "gte": "2016-06-01",
              "lte": "2016-12-01"
            }
          }
        }
      ]
    }
  },
  "size": 0
}
EOF

# Save output
curl -X POST "user:pass@localhost:9200/*/_search" --data "@requestbody.json" > res.json

# Show the output, in all its complexity
cat res.json | jq .

# Parse into csv
cat res.json | jq '.aggregations.timestamp.buckets[] | .key_as_string as $time_period | .user_id.buckets[] | .key as $user_id | .metric.buckets[] | [$time_period, $user_id, .key, .doc_count] | @csv ' -r