How to query a structured field type string in Athena

Question

I have a field with this structure:
Field Name: rulegrouplist
DDL: array,nonterminatingmatchingrules:array,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

Shubh · Answer

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.

How to query a structured field type string in Athena

Relevant content