Strings of arrays and maps?-CodePudding

I have a table that contains a field of a varchar type. Within this field, there are empty arrays, arrays and maps stored as strings. something like this:

id	dumped_field
1	[]
2	[123,456,789]
3	{'0":123, "1":456}
4	NULL

The goal would be to try and convert this string field as an array as opposed to a string:

id	dumped_field
1	[]
2	[123,456,789]
3	[123,456]
4	NULL

The problem is that these various data types have been stored as strings into this field. Is there a way to A) convert the string of array into an array and B) convert the string of json into an array?

CodePudding user response：

Assuming your data is json (and after fixing the quotes in the object) you can process it as json (leveraging try and try_cast):

-- sample data
WITH dataset (id, dumped_field) AS (
   VALUES (1,   '[]'),
        (2, '[123,456,789]'),
        (3, '{"0":123, "1":456}'),
        (4, NULL)
)

-- query
select coalesce(
    try_cast(json_parse(dumped_field) as array(varchar)), --  try process as array
    try(map_values(cast(json_parse(dumped_field) as map(varchar, varchar)))))  --  try process as object
from dataset;

Output:

_col0
[]
[123, 456, 789]
[123, 456]
NULL