-- name: test_array_map_1

CREATE TABLE t1 (

k1 bigint,

c1 array < varchar(65536) >

) ENGINE = OLAP

DUPLICATE KEY(k1) PROPERTIES (

"replication_num" = "1"

);

-- result:

-- !result

CREATE TABLE t2 (

k1 bigint,

c1 bigint

) ENGINE = OLAP

DUPLICATE KEY(k1) PROPERTIES (

"replication_num" = "1"

);

-- result:

-- !result

insert into t1

values

(1, ["1","2"] ),

(2, ["0","2","1"] ),

(3, ["0","2","1"] ),

(4, ["1","2"] ),

(5, ["0","2","1"] ),

(6, ["0","2","1","1"]),

(7, ["0","2","1"] ),

(8, ["1","2"] ),

(9, ["L","2","1"] ),

(10, ["1","2"] );

-- result:

-- !result

insert into t2

values

(1, 1),

(2, 1),

(3, 3),

(4, 5);

-- result:

-- !result

with w1 as (

select

k1, c1, array_map (x -> true, c1) as c2

from

t1

)

select

w1.*

from

w1

join [broadcast] t2 using(k1)

where

array_sum(w1.c1) <= t2.c1

order by

w1.k1;

-- result:

3 ["0","2","1"] [1,1,1]

4 ["1","2"] [1,1]

-- !result

INSERT INTO t1 (k1, c1)

VALUES

(1, ARRAY_MAP(

x -> CAST(x AS STRING),

ARRAY_GENERATE(1, 1000)

)),

(2, ARRAY_MAP(

x -> CAST(x AS STRING),

ARRAY_GENERATE(1, 1000)

)),

(3, ARRAY_MAP(

x -> CAST(x AS STRING),

ARRAY_GENERATE(1, 1000)

)),

(4, ARRAY_MAP(

x -> CAST(x AS STRING),

ARRAY_GENERATE(1, 1000)

)),

(5, ARRAY_MAP(

x -> CAST(x AS STRING),

ARRAY_GENERATE(1, 1000)

));

-- result:

-- !result

CREATE TABLE table1 (

id INT,

arr_largeint ARRAY<INT> NOT NULL

)PROPERTIES ("replication_num" = "1");

-- result:

-- !result

INSERT INTO table1 (id, arr_largeint) VALUES

(1, [1, 2]),

(2, [3, 4, 5]),

(3, [6]);

-- result:

-- !result

CREATE TABLE table2 (

id INT,

arr_str ARRAY<INT> NOT NULL

) PROPERTIES ("replication_num" = "1");

-- result:

-- !result

INSERT INTO table2 (id, arr_str) VALUES

(1, [1, 2, 3]),

(2, [4, 5]),

(3, [6, 7, 8, 9]);

-- result:

-- !result

SELECT t1.id AS t1_id, t2.id AS t2_id, t1.arr_largeint

FROM table1 t1

LEFT JOIN[broadcast] table2 t2

ON t1.id = t2.id

AND array_length(array_map(x -> x + array_length(t2.arr_str), t1.arr_largeint)) >= 2;

-- result:

1 1 [1,2]

2 2 [3,4,5]

3 None [6]

-- !result

WITH `CTE` AS (

SELECT TRUE AS bool_1, TRUE AS bool_2, TRUE AS bool_3, ["a"] AS arr

UNION ALL

SELECT TRUE AS bool_1, TRUE AS bool_2, TRUE AS bool_3, ["a"] AS arr

) SELECT ARRAY_MAP((arg)->`bool_1` AND `bool_2` AND `bool_3`, arr), ARRAY_MAP((arg)->`bool_1` AND `bool_3` AND `bool_2`, arr) FROM `CTE`;

-- result:

[1] [1]

[1] [1]

-- !result

with t1 as (

select parse_json('[{"open_id": "aaa", "num": 1},{"open_id": "bbb", "num": 2},{"open_id": "ccc", "num": 3}]') as price_list

),t2 as (

select price_list,array_map(x -> get_json_string(x,'$.open_id'), cast(price_list as array<json>) ) as fields

from t1

)

select * from t2

where array_contains(fields,'bbb');

-- result:

[{"num": 1, "open_id": "aaa"}, {"num": 2, "open_id": "bbb"}, {"num": 3, "open_id": "ccc"}] ["aaa","bbb","ccc"]

-- !result