REGEXP_EXTRACT函数的问题 [英] Issue with REGEXP_EXTRACT function
问题描述
我在表中存储的字段如下:
id(array< struct< peopleidl:string,householdidl :string>>)
因此示例结果如下:
[{peopleidl = Xi3020rmDOhU2iWYUuu3AXytMOggv6jdRK8_xyzy_COup1vd3uU0-OcWz4C3vW-ew9IeZEN,familyidl = null}]
> $
我正在尝试使用 REGEXP_EXTRACT
函数捕获 = $ c $之间的所有内容c>符号和,
,因此新字段将只是 Xi3020rmDOhh2iWYUu3AXytMOggv6jdRK8_xyzy_COup1vd3uU0-OcWz4C3vW-ew9IeZEN
$ b 但是当我尝试:
REGEXP_EXTRACT(idls,'=(。+),' )AS id
鉴于我在这里进行的测试,正则表达式部分应该是正确的:
但是在运行查询时遇到此错误:
SYNTAX_ERROR:行1:8:函数
regexp_extract的意外参数(array(row(row(peopleidl
varchar,householdidl varchar)),varchar(6),integer))。预期:regexp_extract(varchar(x),JoniRegExp,
bigint),regexp_extract(varchar(x),JoniRegExp)
有人知道我将如何完成我所需要的吗?
解决方案老实说,我不知道为什么要做您使用regexp函数来检索数据。您应该只使用点符号和取消嵌套功能来做到这一点。
https://docs.aws.amazon.com/athena/latest/ug/rows-and-structs.html
I have a field in a table being stored as such:
id (array<struct<peopleidl:string,householdidl:string>>)
So a sample result looks like this:
[{peopleidl=Xi3020rmDOhU2iWYUu3AXytMOggv6jdRK8_xyzy_COup1vd3uU0-OcWz4C3vW-ew9IeZEN, householdidl=null}]
I'm attempting to use the REGEXP_EXTRACT
function to capture everything between the =
sign and the ,
so the new field would simply be Xi3020rmDOhU2iWYUu3AXytMOggv6jdRK8_xyzy_COup1vd3uU0-OcWz4C3vW-ew9IeZEN
But when I try:
REGEXP_EXTRACT(idls, '=(.+),') AS id
The Regex part should be correct given what I tested here:
But when I run the query I'm met with this error:
SYNTAX_ERROR: line 1:8: Unexpected parameters (array(row(peopleidl
varchar,householdidl varchar)), varchar(6), integer) for function
regexp_extract. Expected: regexp_extract(varchar(x), JoniRegExp,
bigint) , regexp_extract(varchar(x), JoniRegExp)
Does anyone know how I might be able to accomplish what I need?
解决方案 To be honest, I don't know why do you use regexp function to retrieve the data. You should just use dot notation and unnest function to do this.
https://docs.aws.amazon.com/athena/latest/ug/rows-and-structs.html
这篇关于REGEXP_EXTRACT函数的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!