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
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.