如何使用红移正则表达式来获取数组中的数字

Question

在redshift中，我有一列包含类似[1，2，3]的数组字符串，我想使用redshift的regex功能返回1,2,3。如何做到这一点？我不想这样做：

SELECT LISTAGG(option_name , ',') WITHIN GROUP (ORDER BY option_name) as pets_names
FROM reference.vital_options
WHERE option_id in
(
-- this nested CTE splits the json string array into comma separated pet ids
with NS AS (
SELECT vo.option_id + 1 as n
FROM <column with number id> as vo
WHERE upper(vo.country) = 'US'
...
)
select TRIM(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(u.pets_vital, NS.n - 1)) AS val
FROM NS
INNER JOIN go_prod.users AS u ON NS.n <= JSON_ARRAY_LENGTH(u.pets_vital)
WHERE u.id = %(user_id)s
)
AND ...

Answer 1

您所要做的只是移除方括号吗？如果是这样，那么translate((函数很可能就是您想要使用的。例如：

create table test as (select '[1,2,3]'::text as A);
select a, translate(a, '][', '') as b from test;