Clickhouse; Issues listing ip addresses where the column has multiple ip addresses

Question

I am trying to get a list of all of our users' IPv4 addresses in a Clickhouse Table. Since some of our users have multiple IPv4 addresses separated by a comma, I created this query:

SELECT 
    IF(ip LIKE '%,%', arrayElement(splitByChar(',', ip), 1), ip) AS ip
FROM users

but it gives me this error: Nested type Array(String) cannot be inside Nullable type.

is it possible to only select a portion of the string like this?

Answer 1

Consider using assumeNotNull-function:

SELECT ips IS NULL ? NULL : IF(ips LIKE '%,%', splitByChar(',', assumeNotNull(ips))[1], ips) AS ip
FROM
(
    /* Emulate the test dataset. */
    SELECT arrayJoin([NULL, '', '1.1.1.1', '2.2.2.2,3.3.3.3']) AS ips
)

/*
┌─ip──────┐
│ ᴺᵁᴸᴸ    │
│         │
│ 1.1.1.1 │
│ 2.2.2.2 │
└─────────┘
*/

---

SELECT ips IS NULL ? NULL : splitByChar(',', assumeNotNull(ips))[1] AS ip
FROM
(
    SELECT arrayJoin([NULL, '', '1.1.1.1', '2.2.2.2,3.3.3.3']) AS ips
)

/*
┌─ip──────┐
│ ᴺᵁᴸᴸ    │
│         │
│ 1.1.1.1 │
│ 2.2.2.2 │
└─────────┘
*/