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
已提問 1 年前檢視次數 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
已回答 1 年前
  • 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.

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南