Upsolver
Search…
STRING_MIN_EACH
Collects the minimum value provided in VALUE for each GROUP.

Syntax

STRING_MIN_EACH ([MAX VALUES, ]GROUP, VALUE)

Arguments

MAX VALUES: An expression of INTEGERtype for how many values to return. The default is adequate in most cases. MAX_VALUES is a "soft" limit, which may be used in order to optimize response times. GROUP: A grouping field. VALUE: An expression of a STRING type that can be ordered.

Returns

An array of pairs with a key of type GROUP and a corresponding value of type STRING.

Example

Data

1
[
2
{
3
"GROUP_ID":"G1",
4
"USER_ID":"U1",
5
"CONNECTION_TIME":"2020-06-26 02:31:29,573"
6
},
7
{
8
"GROUP_ID":"G1",
9
"USER_ID":"U2",
10
"CONNECTION_TIME":"2020-06-26 18:11:45,783"
11
},
12
{
13
"GROUP_ID":"G2",
14
"USER_ID":"Z1",
15
"CONNECTION_TIME":"2020-06-26 23:54:27,687"
16
},
17
{
18
"GROUP_ID":"G2",
19
"CONNECTION_TIME":"2020-07-26 23:54:27,687"
20
},
21
{
22
"GROUP_ID":"G1",
23
"USER_ID":"U2",
24
"CONNECTION_TIME":"2021-07-01 02:31:29,573"
25
},
26
{
27
"GROUP_ID":"G1",
28
"USER_ID":"U1",
29
"CONNECTION_TIME":"2021-07-01 18:11:45,783"
30
},
31
{
32
"GROUP_ID":"G2",
33
"USER_ID":"Z1",
34
"CONNECTION_TIME":"2021-07-01 23:54:27,687"
35
}
36
]
Copied!

Query

When was the most recent entry for each group (serverIp):
1
SET
2
DATE_TIME = TO_DATE(data.CONNECTION_TIME);
3
SELECT
4
STRING_MIN_EACH(data.GROUP_ID, data.CONNECTION_TIME) AS string_min_each_data_group_id__data_connection_time
5
FROM
6
"SAMPLE_DATA_G1U1 - json"
Copied!

Results

1
{
2
"string_min_each_data_group_id__data_connection_time":[
3
{
4
"key":"G1",
5
"value":"2021-07-01 18:11:45,783"
6
},
7
{
8
"key":"G2",
9
"value":"2021-07-01 23:54:27,687"
10
}
11
]
12
}
Copied!

Dialog

Last modified 8mo ago