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.

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ