-
Notifications
You must be signed in to change notification settings - Fork 24.3k
/
mv_expand.csv-spec
318 lines (266 loc) · 11.2 KB
/
mv_expand.csv-spec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
simple
// tag::simple[]
ROW a=[1,2,3], b="b", j=["a","b"]
| MV_EXPAND a
// end::simple[]
;
// tag::simple-result[]
a:integer | b:keyword | j:keyword
1 | b | ["a", "b"]
2 | b | ["a", "b"]
3 | b | ["a", "b"]
// end::simple-result[]
;
twice
row a=[1,2,3], b="b", j=["a","b"] | mv_expand a | mv_expand j;
a:integer | b:keyword | j:keyword
1 | b | "a"
1 | b | "b"
2 | b | "a"
2 | b | "b"
3 | b | "a"
3 | b | "b"
;
explosion
row
a = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
b = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
c = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
d = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
e = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
f = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
g = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
x = 10000000000000
| mv_expand a | mv_expand b | mv_expand c | mv_expand d | mv_expand e | mv_expand f | mv_expand g
| limit 10;
a:integer | b:integer | c:integer | d:integer | e:integer | f:integer | g:integer | x:long
1 | 1 | 1 | 1 | 1 | 1 | 1 | 10000000000000
1 | 1 | 1 | 1 | 1 | 1 | 2 | 10000000000000
1 | 1 | 1 | 1 | 1 | 1 | 3 | 10000000000000
1 | 1 | 1 | 1 | 1 | 1 | 4 | 10000000000000
1 | 1 | 1 | 1 | 1 | 1 | 5 | 10000000000000
1 | 1 | 1 | 1 | 1 | 1 | 6 | 10000000000000
1 | 1 | 1 | 1 | 1 | 1 | 7 | 10000000000000
1 | 1 | 1 | 1 | 1 | 1 | 8 | 10000000000000
1 | 1 | 1 | 1 | 1 | 1 | 9 | 10000000000000
1 | 1 | 1 | 1 | 1 | 1 | 10 | 10000000000000
;
explosionStats
row
a = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
b = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
c = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
d = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
e = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
x = 10000000000000
| mv_expand a | mv_expand b | mv_expand c | mv_expand d | mv_expand e
| stats sum_a = sum(a) by b
| sort b;
//12555000 = sum(1..30) * 30 * 30 * 30
sum_a:long | b:integer
12555000 | 1
12555000 | 2
12555000 | 3
12555000 | 4
12555000 | 5
12555000 | 6
12555000 | 7
12555000 | 8
12555000 | 9
12555000 | 10
12555000 | 11
12555000 | 12
12555000 | 13
12555000 | 14
12555000 | 15
12555000 | 16
12555000 | 17
12555000 | 18
12555000 | 19
12555000 | 20
12555000 | 21
12555000 | 22
12555000 | 23
12555000 | 24
12555000 | 25
12555000 | 26
12555000 | 27
12555000 | 28
12555000 | 29
12555000 | 30
;
expandAfterSort1
from employees | keep job_positions, emp_no | sort emp_no | mv_expand job_positions | limit 10 | sort job_positions;
job_positions:keyword |emp_no:integer
Accountant |10001
Head Human Resources |10004
Principal Support Engineer|10006
Reporting Analyst |10004
Senior Python Developer |10001
Senior Team Lead |10002
Support Engineer |10004
Tech Lead |10004
null |10005
null |10003
;
expandAfterSort2
from employees | sort emp_no | mv_expand job_positions | keep job_positions, emp_no | limit 5;
job_positions:keyword |emp_no:integer
Accountant |10001
Senior Python Developer|10001
Senior Team Lead |10002
null |10003
Head Human Resources |10004
;
expandWithMultiSort
from employees | keep emp_no, job_positions | sort emp_no | mv_expand job_positions | limit 10 | where emp_no <= 10006 | sort job_positions nulls first;
emp_no:integer | job_positions:keyword
10003 |null
10005 |null
10001 |Accountant
10004 |Head Human Resources
10006 |Principal Support Engineer
10004 |Reporting Analyst
10001 |Senior Python Developer
10002 |Senior Team Lead
10004 |Support Engineer
10004 |Tech Lead
;
filterMvExpanded
from employees | keep emp_no, job_positions | mv_expand job_positions | where job_positions like "A*" | sort job_positions, emp_no;
emp_no:integer | job_positions:keyword
10001 |Accountant
10012 |Accountant
10016 |Accountant
10023 |Accountant
10025 |Accountant
10028 |Accountant
10034 |Accountant
10037 |Accountant
10044 |Accountant
10045 |Accountant
10050 |Accountant
10051 |Accountant
10066 |Accountant
10081 |Accountant
10085 |Accountant
10089 |Accountant
10092 |Accountant
10094 |Accountant
10010 |Architect
10011 |Architect
10031 |Architect
10032 |Architect
10042 |Architect
10047 |Architect
10059 |Architect
10068 |Architect
10072 |Architect
10076 |Architect
10078 |Architect
10096 |Architect
10098 |Architect
;
doubleSort_OnDifferentThan_MvExpandedFields
from employees | sort emp_no | mv_expand job_positions | keep emp_no, job_positions, salary | sort salary, job_positions | limit 5;
emp_no:integer | job_positions:keyword |salary:integer
10015 |Head Human Resources |25324
10015 |Junior Developer |25324
10015 |Principal Support Engineer|25324
10015 |Support Engineer |25324
10035 |Data Scientist |25945
;
doubleLimit_expandLimitLowerThanAvailable
from employees | where emp_no == 10004 | limit 1 | keep emp_no, job_positions | mv_expand job_positions | limit 2;
emp_no:integer | job_positions:keyword
10004 |Head Human Resources
10004 |Reporting Analyst
;
doubleLimit_expandLimitGreaterThanAvailable
from employees | where emp_no == 10004 | limit 1 | keep emp_no, job_positions | mv_expand job_positions | limit 5;
emp_no:integer | job_positions:keyword
10004 |Head Human Resources
10004 |Reporting Analyst
10004 |Support Engineer
10004 |Tech Lead
;
doubleLimitWithSort
from employees | where emp_no == 10004 | limit 1 | keep emp_no, job_positions | mv_expand job_positions | limit 5 | sort job_positions desc;
emp_no:integer | job_positions:keyword
10004 |Tech Lead
10004 |Support Engineer
10004 |Reporting Analyst
10004 |Head Human Resources
;
tripleLimit_WithWhere_InBetween_MvExpand_And_Limit
from employees | where emp_no == 10004 | limit 1 | keep emp_no, job_positions | mv_expand job_positions | where job_positions LIKE "*a*" | limit 2 | where job_positions LIKE "*a*" | limit 3;
emp_no:integer | job_positions:keyword
10004 |Head Human Resources
10004 |Reporting Analyst
;
expandFoldable
row a = "foobar", b = ["foo", "bar"], c = 12 | mv_expand b | where b LIKE "fo*";
a:keyword | b:keyword | c:integer
foobar | foo | 12
;
expandEvalFoldable
from employees | sort emp_no | limit 2 | eval foldable = "foo,bar" | eval generate_mv = split(foldable,",") | mv_expand generate_mv | keep emp_no, first_name, generate_mv | sort emp_no asc, generate_mv desc;
emp_no:integer | first_name:keyword | generate_mv:keyword
10001 | Georgi | foo
10001 | Georgi | bar
10002 | Bezalel | foo
10002 | Bezalel | bar
;
expandEvalFoldableWhere
from employees | sort emp_no | limit 2 | eval foldable = "foo,bar" | eval generate_mv = split(foldable,",") | mv_expand generate_mv | keep emp_no, first_name, generate_mv | where generate_mv LIKE "fo*";
emp_no:integer | first_name:keyword | generate_mv:keyword
10001 | Georgi | foo
10002 | Bezalel | foo
;
// see https://github.com/elastic/elasticsearch/issues/102120
expandAfterDuplicateAggs#[skip:-8.11.99]
row a = 1 | stats a = count(*), b = count(*) | mv_expand b;
a:long | b:long
1 | 1
;
expandAfterDuplicateAggs2#[skip:-8.11.99]
row a = 1 | stats a = count(*), b = count(*) | mv_expand a;
a:long | b:long
1 | 1
;
expandAfterDuplicateAggsAndEval#[skip:-8.11.99]
row a = 1 | stats a = count(*), b = count(*) | eval c = 2 | mv_expand b;
a:long | b:long | c:integer
1 | 1 | 2
;
expandAfterDuplicateAggsComplex#[skip:-8.11.99]
row x = [1, 2, 3]
| mv_expand x
| stats a = count(*), b = count(*), c = count(*)
| eval x = a + c + b
| mv_expand a
| rename a AS a | drop a;
b:long | c:long | x:long
3 | 3 | 9
;
expandAfterDuplicateAggsMultirow#[skip:-8.11.99]
from employees
| stats a = count(gender), b = count(*), c = count(*) by gender
| eval str = concat(to_string(b), ",", gender)
| mv_expand b
| eval x = split(str,",")
| mv_expand x
| sort x;
a:long | b:long | c:long | gender:keyword | str:keyword | x:keyword
33 |33 |33 |F |"33,F" |33
57 |57 |57 |M |"57,M" |57
33 |33 |33 |F |"33,F" |F
57 |57 |57 |M |"57,M" |M
0 |10 |10 |null |null |null
;
//see https://github.com/elastic/elasticsearch/issues/102912
statsDissectThatOverwritesAndMvExpand#[skip:-8.11.99]
row a = "a", b = 1 | stats e = min(b) by a | dissect a "%{e}" | mv_expand e;
a:keyword | e:keyword
a | a
;