Presto SQL如何提取字符串数组列的第一、第二个元素为单独列？

直接执行cast(items as array(varchar))失败，是因为数据库无法将带方括号、单引号的普通字符串识别为合法的数组字面量，得先处理格式再转换，或者直接提取目标元素，以下是几种可行方案：

通用SQL方案（适配多数数据库） #

正则表达式提取 #

通过正则匹配直接捕获第一个和第二个元素：

SELECT
  REGEXP_REPLACE(items, '^\[''([^'']+)''.*$', '\1') AS first_item,
  REGEXP_REPLACE(items, '^\[''[^'']+'',''([^'']+)''.*$', '\1') AS second_item
FROM your_table;

如果提取结果带单引号，再加一层TRIM清理：

SELECT
  TRIM(BOTH '''' FROM REGEXP_REPLACE(items, '^\[''([^'']+)''.*$', '\1')) AS first_item,
  TRIM(BOTH '''' FROM REGEXP_REPLACE(items, '^\[''[^'']+'',''([^'']+)''.*$', '\1')) AS second_item
FROM your_table;

字符串分割提取 #

先去掉首尾的方括号，再按','分割后取对应位置的元素：

SELECT
  TRIM(BOTH '''' FROM SPLIT_PART(TRIM(BOTH '[]' FROM items), ''',''', 1)) AS first_item,
  TRIM(BOTH '''' FROM SPLIT_PART(TRIM(BOTH '[]' FROM items), ''',''', 2)) AS second_item
FROM your_table;

• TRIM(BOTH '[]' FROM items)：去掉字符串首尾的[和]
• SPLIT_PART(..., ''',''', 1)：按','分割字符串，取第1个分片
• TRIM(BOTH '''' FROM ...)：去掉分片首尾的单引号

特定数据库专用方案 #

PostgreSQL #

用string_to_array函数转换为数组后提取元素：

SELECT
  TRIM(BOTH '''' FROM (string_to_array(TRIM(BOTH '[]' FROM items), ''','''))[1]) AS first_item,
  TRIM(BOTH '''' FROM (string_to_array(TRIM(BOTH '[]' FROM items), ''','''))[2]) AS second_item
FROM your_table;

或者用regexp_split_to_array一步到位：

SELECT
  (regexp_split_to_array(items, '^\[''|''\]$|'','''))[2] AS first_item,
  (regexp_split_to_array(items, '^\[''|''\]$|'','''))[3] AS second_item
FROM your_table;

Spark SQL #

先将单引号替换为双引号（符合JSON格式），再用from_json转成数组：

SELECT
  element_at(from_json(replace(items, '''', '"'), 'array<string>'), 1) AS first_item,
  element_at(from_json(replace(items, '''', '"'), 'array<string>'), 2) AS second_item
FROM your_table;

也可以用get_json_object直接提取：

SELECT
  get_json_object(replace(items, '''', '"'), '$[0]') AS first_item,
  get_json_object(replace(items, '''', '"'), '$[1]') AS second_item
FROM your_table;

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