Convert from struct format to string

1

I got the below json string and need to get the numbers of “:” symbols under notifications key
first i have to convert the below json string into struct format and then do the symbols count

but i was failed to convert below struct into string format, pls advise if flatten , to_char , unnest function support this operation? thanks

“notifications”: {“4”: 1, “5”: 1, “6”: 1, “18”: 1, “21”: 1, “25”: 1, “30”: 1, “31”: 1, “32”: 1, “33”: 1, “34”: 1, “35”: 1, “36”: 1}, “last_access_date”: “2023-11-16T16:58:53+0000”, “marketing_enabled”: true}

cur ver. 19.0 community

2

Try something like this?

SELECT LENGTH(notifications_string) - LENGTH(REPLACE(notifications_string,':','')) FROM (
SELECT CAST(convert_from(convert_to(notifications, 'JSON'), 'UTF8') as VARCHAR) AS notifications_string FROM forum
);
--13

image
image951×372 35.1 KB

3

thanks for help!
it works if the variable is string/ numeric value
but failed for struct field, i checked seems a known bug for ver 19.0.0, will there be workaround

my current flow : convert json to struct, then extract the “notification” key (to exclude other non related key field) and then convert to string to use the replace+length to count the occurence of notification subkey

workaround flow: can I just do the occurrence count on the raw string without convert to struct (count symbol “:” for notification only) and exclude other non-related key

many thanks for kind help ~

image
image998×433 17.6 KB

4

thanks for help
I encounter an issues below:
{“notifications”: {“4”: 1, “5”: 1, “6”: 1, “18”: 1}}

we have a Json string above and I would like to convert all the key and value in a tabular structural format in a dynamic way instead of hardcoded, would you have any idea?
I know dremio have a flatten function but it just support for array value.

lots of thanks!