Histogram Map Aggregations

Note: Histogram map aggregations can only be used in rollups.

A histogram map aggregate consists of two components:

1. An object which maps from keys to values
2. An object which maps from value hash to the number of times the value appears in the key-value map

Therefore, a histogram map aggregate is a regular key-value map supplemented with a value histogram. This means that in addition to key lookups, histogram map aggregates provide the ability to efficiently look up the number of times a given value appears in the key-value map.

HMAP_AGG

HMAP_AGG(key, value, op) Creates a histogram map aggregate. For each input document, the op argument specifies the update semantics for the given key value pair.

The op argument accepts the following string values (case-insensitive):

• 'UPSERT': Adds the key value pair to the object if the key does not already exist in the object. Overwrites the value for the key otherwise.
• 'INSERT': Adds the key value pair to the object if the key does not already exist in the object. Does not overwrite the value if the key already exists in the object.
• 'UPDATE': Updates the value for the key if the key already exists in the object. Does not add the key value pair if the key does not already exist in the object.
• 'DELETE': Deletes the key from the object. If the key does not already exist in the object, this is a no-op.
• 'IGNORE': Ignore this update completely.

The op argument is optional. If it is omitted or set to null or undefined, 'UPSERT' semantics are used.

Suppose we have the following input documents for our rollup collection fruits:

{"fruit": "apple", "color": "green", "op": "INSERT"},
{"fruit": "banana", "color": "yellow", "op": "UPSERT"},
{"fruit": "grape", "color": "purple"},
{"fruit": "orange", "color": "orange", "op": "DELETE"},
{"fruit": "apple", "color": "pink"},
{"fruit": "apple", "color": "red", "op": "INSERT"},
{"fruit": "pear", "color": "green", "op": "UPDATE"},
{"fruit": "grape", "color": "green", "op": "DELETE"},
{"fruit": "banana", "color": "brown", "op": "UPDATE"},
{"fruit": "pear", "color": "brown", "op": "INSERT"}

Given the rollup query:

SELECT HMAP_AGG(fruit, color, op) hmap
FROM _input

The key-value map component of the created histogram map aggregate will be as follows:

{
  {"fruit": "apple", "color": "pink"},
  {"fruit": "banana", "color": "brown"},
  {"fruit": "pear", "color": "brown"}
}

The value histogram component of the created histogram map aggregate can be queried to see that the value 'pink' occurs once and the value 'brown' occurs twice in the key-value map.

HMAP_ELEMENT_AT

HMAP_ELEMENT_AT(hmap, k) Returns the value associated with key k in the histogram map aggregate hmap.

Using the same rollup example as above,

SELECT HMAP_ELEMENT_AT(hmap, 'apple')
FROM fruits

SELECT HMAP_ELEMENT_AT(hmap, 'strawberry')
FROM fruits

HMAP_VALUE_COUNT

HMAP_VALUE_COUNT(hmap, v) Returns the number of times value v occurs in the histogram map aggregate hmap.

Using the same rollup example as above,

SELECT HMAP_VALUE_COUNT(hmap, 'brown')
FROM fruits

SELECT HMAP_VALUE_COUNT(hmap, 'blue')
FROM fruits

HMAP_CONTAINS_VALUE

HMAP_CONTAINS_VALUE(hmap, v) Returns whether value v occurs in the histogram map aggregate hmap.

Using the same rollup example as above,

SELECT HMAP_CONTAINS_VALUE(hmap, 'pink')
FROM fruits

SELECT HMAP_CONTAINS_VALUE(hmap, 'orange')
FROM fruits