如何实现数组类型的like操作

[U_1682668219516_2468]

例如ip字段是数据类型，里面有数据[192.168.1.1, 172.168.1.1]这两条数据，我想要筛选出数组中包含’192’这个数据，这种方式有办法通过sql来实现吗

[jingdan]

可以用unnest展开试试

CREATE TABLE `student_sr` (
  `id` bigint(20) NULL COMMENT "",
  `scores` ARRAY<string> NULL COMMENT ""
) ENGINE=OLAP 
DUPLICATE KEY(`id`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`id`) BUCKETS 1 
PROPERTIES (
"replication_num" = "3"
);

INSERT INTO student_sr VALUES
(1, ['80','85','87']),
(2, ['77', null, '89']),
(3, null),
(4, null),
(5, ['90','92']);

select * from (select id, scores, unnest as b FROM student_sr, unnest(scores)) tb where b like '%8%';