COLLECT_SET_EACH

A set of all distinct entries by the given key.

Syntax

COLLECT_SET([MAX VALUES, ]GROUP, VALUE)

Arguments

MAX VALUES: The maximum number of entries that can be counted (default: 2,147,483,647) ‌ MAX VALUESis optional. If it is omitted there is, in effect, no limit. GROUP: Find the distinct entries of this field. VALUE: This field is the key.

Returns

An ARRAY of the argument type.

Example

Data

[
   {
      "GROUP_ID":"G1",
      "USER_ID":"U1",
      "CONNECTION_TIME":"2020-06-26 02:31:29,573"
   },
   {
      "GROUP_ID":"G1",
      "USER_ID":"U2",
      "CONNECTION_TIME":"2020-06-26 18:11:45,783"
   },
   {
      "GROUP_ID":"G2",
      "USER_ID":"Z1",
      "CONNECTION_TIME":"2020-06-26 23:54:27,687"
   },
   {
      "GROUP_ID":"G2",
      "CONNECTION_TIME":"2020-07-26 23:54:27,687"
   },
   {
      "GROUP_ID":"G1",
      "USER_ID":"U2",
      "CONNECTION_TIME":"2021-07-01 02:31:29,573"
   },
   {
      "GROUP_ID":"G1",
      "USER_ID":"U1",
      "CONNECTION_TIME":"2021-07-01 18:11:45,783"
   },
   {
      "GROUP_ID":"G2",
      "USER_ID":"Z1",
      "CONNECTION_TIME":"2021-07-01 23:54:27,687"
   }
]

Query:

Display all the VALUE by key.

SELECT
   COLLECT_SET_EACH(data.GROUP_ID, data.USER_ID) AS collect_set_each_data_group_id__data_user_id 
FROM
   "SAMPLE_DATA_G1U1 - json"

Results:

{
   "collect_set_each_data_group_id__data_user_id":[
      {
         "key":"G1",
         "value":[
            "U1",
            "U2"
         ]
      },
      {
         "key":"G2",
         "value":[
            "Z1"
         ]
      }
   ]
}

Related Function

COLLECT_SET

Dialog