Spark SQL中用split()处理带括号引号的字符串转数组问题

输入数据格式 #

- ["some val1"]
- []
- ["some val2", "some_val3"]
- single value without brackets and quotes
- [[]
- ["val with "quote 1", "val with "quote and bracket["]

目标输出格式 #

- 0: some val1 
- 0: null
- 0: some val2  
  1: some val3
- 0: single value without brackets and quotes
- 0: [
- 0: val with "quote 1
  1: val with "quote and bracket[

用户尝试的代码 #

初始尝试 #

with cte as
(select '["some val1"]' as val
 union all
 select '[]'
 union all
 select '["some val2", "some_val3"]'
 union all
 select 'single value without brackets and quotes'
 union all
 select '[[]'
 union all
 select '["val with "quote 1", "val with "quote and bracket["]')

select
  split(ltrim('[', rtrim(']', val)), ',') as array_val
from cte

更新后的尝试 #

transform(split(regexp_replace(val, '^[\[]|[\]]$', ''), ','), x -> regexp_replace(x, '^["]|["]$', ''))

解决方案 #

针对需求（移除字符串外围一对括号、数组元素外围一对引号，保留内部特殊字符，处理空数组为null），可以用以下Spark SQL代码实现：

with cte as
(select '["some val1"]' as val
 union all
 select '[]'
 union all
 select '["some val2", "some_val3"]'
 union all
 select 'single value without brackets and quotes'
 union all
 select '[[]'
 union all
 select '["val with "quote 1", "val with "quote and bracket["]')

select
  case
    when cleaned = '' then array(null)
    else transform(
           split(cleaned, '",\\s*"'),
           x -> regexp_replace(x, '^"|"$', '')
         )
  end as array_val
from (
  select regexp_replace(val, '^\\[|\\]$', '') as cleaned
  from cte
) t

代码说明 #

1. 清理外围括号：通过regexp_replace(val, '^\\[|\\]$', '')移除字符串首尾的[和]，无首尾括号的字符串保持不变。
2. 处理空数组：清理后为空字符串（对应原输入[]）时，返回包含null的数组。
3. 分割元素：用",\\s*"作为分隔符分割字符串，适配元素间有空格或无空格的情况。
4. 移除元素外围引号：通过transform遍历数组，用regexp_replace(x, '^"|"$', '')移除每个元素首尾的引号，保留内部的引号、括号等字符。

内容的提问来源于stack exchange，提问作者archjkeee