Help with query aggregation for data table

[bruce289]

Hi,

Following are 3 example documents containing the total memory on a host and the hypervisor it is hosted on. I would like to create a table visualisation that shows the amount of unassigned memory on each hypervisor, but I haven't been able to figure out how to do it

Documents:

{
  "host" : {
    "name : "guest-1"
  },
  "memory" : {
    "total" : 16
  },
  "hypervisor" : "hypervisor-1"
}

{
  "host" : {
    "name : "guest-2"
  },
  "memory" : {
    "total" : 32
  },
  "hypervisor" : "hypervisor-1"
}

{
  "host" : {
    "name : "hypervisor-1"
  },
  "memory" : {
    "total" : 64
  }
}

Required data table output given the documents above:

Hypervisor    | Unassigned memory
--------------+------------------
hypervisor-1  | 16

It would be great if someone could point me in the right direction here. I'm able to get the total memory used by guests by grouping by hypervisor, but I don't know how to then subtract that from the total memory on the hypervisor. Or maybe that's the wrong way to go about it. Any ideas would be much appreciated.

Thanks.