nested agg scratch pad, some of these may not even be correct

gistfile1.txt

DELETE shoe_stores

PUT shoe_stores
{
  "mappings": {
    "properties": {
      "store_id": {
        "type": "keyword"
      },
      "shoes": {
        "type": "nested",
        "properties": {
          "shoe_name": {
            "type": "keyword"
          },
          "in_stock": {
            "type": "nested"
          }
        }
      },
      "socks": {
        "type": "nested",
        "properties": {
          "sock_name": {
            "type": "keyword"
          },
          "in_stock": {
            "type": "nested"
          }
        }
      }
    }
  }
}

POST shoe_stores/_doc
{
  "store_id": 1,
  "shoes": [
    {
      "shoe_name": "nike air",
      "in_stock": [
        {
          "size": 12,
          "color": "black"
        },
        {
          "size": 10,
          "color": "grey"
        },
        {
          "size": 10,
          "color": "white"
        }
      ]
    },
    {
      "shoe_name": "nike zoom",
      "in_stock": [
        {
          "size": 9,
          "color": "grey"
        },
        {
          "size": 10,
          "color": "white"
        },
        {
          "size": 12,
          "color": "green"
        }
      ]
    }
  ],
  "socks": [
    {
      "sock_name": "nike",
      "in_stock": [
        {
          "size": "s",
          "color": "white"
        },
        {
          "size": "m",
          "color": "white"
        },
        {
          "size": "m",
          "color": "black"
        }
      ]
    }
  ]
}


POST shoe_stores/_doc
{
  "store_id": 2,
  "shoes": [
    {
      "shoe_name": "adidas samba",
      "in_stock": [
        {
          "size": 12,
          "color": "black"
        },
        {
          "size": 13,
          "color": "black"
        }
      ]
    }
  ],
  "socks": [
    {
      "sock_name": "nike",
      "in_stock": [
        {
          "size": "s",
          "color": "white"
        },
        {
          "size": "m",
          "color": "white"
        },
        {
          "size": "m",
          "color": "black"
        }
      ]
    }
  ]
}

# Count of shoes across stores
POST shoe_stores/_search 
{
  "size": 0,
  "aggs": {
    "shoes": {
      "nested": {
        "path": "shoes"
      },
      "aggs": {
        "shoe_names": {
          "terms": {
            "field": "shoes.shoe_name"
          }
        }
      }
    }
  }
}

# Count of SKUs per size
POST shoe_stores/_search
{
  "size": 0,
  "aggs": {
    "shoes": {
      "nested": {
        "path": "shoes.in_stock"
      },
      "aggs": {
        "instock": {
          "terms": {
            "field": "shoes.in_stock.size"
          }
        }
      }
    }
  }
}

# Count of shoes per size
POST shoe_stores/_search
{
  "size": 0,
  "aggs": {
    "shoes": {
      "nested": {
        "path": "shoes.in_stock"
      },
      "aggs": {
        "instock": {
          "terms": {
            "field": "shoes.in_stock.size"
          },
          "aggs": {
            "reverse": {
              "reverse_nested": {
                "path": "shoes"
              },
              "aggs": {
                "shoe_count": {
                  "filter": {
                    "match_all": {}
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

# Count of stores per size
POST shoe_stores/_search
{
  "size": 0,
  "aggs": {
    "shoes": {
      "nested": {
        "path": "shoes.in_stock"
      },
      "aggs": {
        "instock": {
          "terms": {
            "field": "shoes.in_stock.size"
          },
          "aggs": {
            "reverse": {
              "reverse_nested": {},
              "aggs": {
                "store_count": {
                  "filter": {
                    "match_all": {}
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

# Cardinality of store ID per size
# Shows how a metric agg could go under reverse_nested
POST shoe_stores/_search
{
  "size": 0,
  "aggs": {
    "shoes": {
      "nested": {
        "path": "shoes.in_stock"
      },
      "aggs": {
        "instock": {
          "terms": {
            "field": "shoes.in_stock.size"
          },
          "aggs": {
            "reverse": {
              "reverse_nested": {},
              "aggs": {
                "max_id": {
                  "cardinality": {
                    "field": "store_id"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

# Count of sock types per shoe size
POST shoe_stores/_search
{
  "size": 0,
  "aggs": {
    "shoes": {
      "nested": {
        "path": "shoes.in_stock"
      },
      "aggs": {
        "instock": {
          "terms": {
            "field": "shoes.in_stock.size"
          },
          "aggs": {
            "reverse": {
              "reverse_nested": {},
              "aggs": {
                "socks": {
                  "nested": {
                    "path": "socks"
                  },
                  "aggs": {
                    "sock_types": {
                      "terms": {
                        "field": "socks.sock_name"
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}


# Count of sock SKUs per sock size per shoe size
POST shoe_stores/_search
{
  "size": 0,
  "aggs": {
    "shoes": {
      "nested": {
        "path": "shoes.in_stock"
      },
      "aggs": {
        "instock": {
          "terms": {
            "field": "shoes.in_stock.size"
          },
          "aggs": {
            "reverse": {
              "reverse_nested": {},
              "aggs": {
                "socks": {
                  "nested": {
                    "path": "socks.in_stock"
                  },
                  "aggs": {
                    "sock_types": {
                      "terms": {
                        "field": "socks.in_stock.size.keyword"
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

# Count of stores with sock names per shoe size
POST shoe_stores/_search
{
  "size": 0,
  "aggs": {
    "shoes": {
      "nested": {
        "path": "shoes.in_stock"
      },
      "aggs": {
        "instock": {
          "terms": {
            "field": "shoes.in_stock.size"
          },
          "aggs": {
            "reverse": {
              "reverse_nested": {},
              "aggs": {
                "socks": {
                  "nested": {
                    "path": "socks.in_stock"
                  },
                  "aggs": {
                    "sock_types": {
                      "terms": {
                        "field": "socks.in_stock.size.keyword"
                      },
                      "aggs": {
                        "stores": {
                          "reverse_nested": {},
                          "aggs": {
                            "allstores": {
                              "filter": {
                                "match_all": {}
                              }
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}