How to query a structured field type string in Athena

0

I have a field with this structure: Field Name: rulegrouplist DDL: array<struct<rulegroupid:string,terminatingrule:structruleid:string,action:string,rulematchdetails:string,nonterminatingmatchingrules:array<string>,excludedrules:string>>

When I query with:

WITH dataset AS (
	SELECT *
	FROM "Table_Name"
	CROSS JOIN UNNEST(rulegrouplist) AS t(t)
)
SELECT t.excludedrules
FROM "table_name"
WHERE date = '2023/03/10'
	AND t.excludedrules IS NOT NULL
Limit 1

I get this result:

excludedrules

**[{"rulematchdetails":"null","exclusiontype":"EXCLUDED_AS_COUNT","ruleid":"HostingProviderIPList"}]**

I need to run a query where I can inpect one of the element in this field, in this case, excludedrules, which in the DDL is a field type string. I wonder if there's an easier way other than apply string functions, as I did here:

WITH dataset AS (
	SELECT *
	FROM "Table_Name"
	CROSS JOIN UNNEST(rulegrouplist) AS t(t)
)
SELECT trim(translate(translate(split_part(t.excludedrules,'ruleid',2),'":', ''),'}]','')) as rule
FROM dataset
WHERE t.excludedrules IS NOT NULL
	AND date = '2023/03/10'
Limit 1

I get this result:

HostingProviderIPList
MauroJ
질문됨 일 년 전1813회 조회
1개 답변
1

It would have been fairly easy using unnest() but as the data is of type string it is not possible. You have to parse the data as array first because casting won’t be a solution here, Hence from here the question arises that "Is your underlying data also stores the field value for excludedrules as string?"

If it is not than you can change the schema of this field into appropriate array struct. in DDL of your table or else you can run crawler on the underlying source data.

AWS
지원 엔지니어
Shubh
답변함 일 년 전
  • thank you for your reply Shubh. I just have one question: isn't this string value returning something very similar to a JSON structure? For that matter, doesn't a json_string supposed to work? Or even a CAST into a JSON and then json_extract?

    I've tried, but it returns a NULL value.

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠