Elasticsearch, how to return unique values of two fields

Question

I have an index with 20 different fields. I need to be able to pull unique docs where combination of fields "cat" and "sub" are unique. In SQL it would look this way: select unique cat, sub from table A; I can do it for one field this way:

{
"size": 0,
"aggs" : {
    "unique_set" : {
        "terms" : { "field" : "cat" }
    }
}}

but how do I add another field to check uniqueness across two fields?

Thanks,

Answer 1

SQL's SELECT DISTINCT [cat], [sub] can be imitated with a Composite Aggregation.

{
  "size": 0, 
  "aggs": {
    "cat_sub": {
      "composite": {
        "sources": [
          { "cat": { "terms": { "field": "cat" } } },
          { "sub": { "terms": { "field": "sub" } } }
        ]
      }
    }
  }
}

Returns...

"buckets" : [
  {
    "key" : {
      "cat" : "a",
      "sub" : "x"
    },
    "doc_count" : 1
  },
  {
    "key" : {
      "cat" : "a",
      "sub" : "y"
    },
    "doc_count" : 2
  },
  {
    "key" : {
      "cat" : "b",
      "sub" : "y"
    },
    "doc_count" : 3
  }
]

Answer 2

The only way to solve this are probably nested aggregations:

{
"size": 0,
    "aggs" : {
        "unique_set_1" : {

            "terms" : {
                     "field" : "cats"
            },
            "aggregations" : { 
                "unique_set_2": {
                    "terms": {"field": "sub"}
                }
            }
        }
    }

}