I have a table that has a list of dictionaries. In the example there is only one dictionary in the list.
| items |
|---|
| [{'a': 'apple', b: 'banana'}] |
I want to sekect the value for 'a', which is apple.
I have tried:
SELECT json_extract_scalar(json_parse(items), '$.a') which gives the error:
Unexpected parameters (array(row("t" varchar,"i" varchar,"p" integer,"v" integer,"s" varchar))) for function json_parse. Expected: json_parse(varchar(x))
I also tried:
SELECT TRANSFORM(CAST(JSON_PARSE(items) AS ARRAY<JSON>), x -> JSON_EXTRACT_SCALAR(x, '$.a'))
which gives the same error. If I do CAST(items AS varchar) first, there is an error that says it cannot convert an array to varchar.
CodePudding user response：
Provided error shows that your data is not actually a varchar or json but an array of ROW with 5 fields (t, i, p, v, s). Rows can't be easily converted to required json structure, but you can easily access it's fields by name:
select item.t
from (
SELECT array [ cast (row('apple', 'banana') as row(t varchar, i varchar)) ] items -- shortened sample data
)
cross join unnest(items) as c(item) -- flatten the array
Output:
| t |
|---|
| apple |