ATHENA JSON blob with multple columns of the same name

Hello,

Thanks for reaching out.

The json_extract_scalar function does not work with arrays as it only returns scalar values such as boolean and strings, hence when you are passing your array tag, the query is returning blank. Please refer to our documentation [1] to learn more about querying JSON in Athena.

If you want the output to have a single row with concatenated tag names and IDs, you can try the following query:

WITH tag_elements AS (
  SELECT 
    tag,
    CAST(json_extract(tag, concat('$[', CAST(i AS VARCHAR), ']')) AS JSON) AS element
  FROM 
    "database"
  CROSS JOIN 
    UNNEST(sequence(0, json_array_length(tag) - 1)) AS t(i)
)
SELECT 
  array_join(
    array_agg(json_extract_scalar(element, '$.name')),
    ', '
  ) AS Tag,
  array_join(
    array_agg(json_extract_scalar(element, '$.id')),
    ', '
  ) AS Tag_id
FROM 
  tag_elements;

This query uses json_extract function along with concat to extract each element from the JSON array - tag. We generate a sequence of numbers from 0 to the length of the JSON array minus 1 using sequence, and then cross join it to the original table. We then use json_extract_scalar to extract the "name" and "id" values from each element and concatenate them using array_agg and array_join, which returns the desired output.

Hope this information was helpful.

References:

[1] Extracting data from JSON - https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html