Athena UNNEST array into column values-CodePudding

WITH key_value AS
(
    SELECT e
    FROM headers
    CROSS JOIN UNNEST(CAST(json_parse(headers) AS array(json))) t(e)
)
SELECT
    CAST(JSON_PARSE(e) AS map(varchar, varchar)) AS value
FROM
    key_value

{
    "headers": [{
        "key": "Name",
        "value": "xxxx"
    },
    {
        "key": "Email",
        "value": "xxxx"
    },
    {
        "key": "ID",
        "value": "1234"
    },
    {
        "key": "Company",
        "value": "XYZ"
    },
    {
        "key": "Groups",
        "value": "[]"
    },
    {
        "key": "Address",
        "value": "xxxx"
    },
    {
        "key": "State",
        "value": "Log In"
    },
    {
        "key": "Component",
        "value": "xxxx"
    },
    {
        "key": "LastUsed",
        "value": "xxxx"
    }]
}

So far I have tried this code, the first part seems to work which seems to give me the innest JSON objects but can convert them to individual column objects.

I need to turn each headers array into a row with key as column and values as data.

CodePudding user response：

You can apply second unnest (notice succinct syntax) to the map which flattens data into to columns:

SELECT k, v
FROM key_value,
unnest(CAST(e AS map(varchar, varchar))) as t(k, v)

CodePudding user response：

    WITH key_value AS
(
    SELECT "$path" as ID,
    e
    FROM headers
    CROSS JOIN UNNEST(CAST(json_parse(headers) AS array(json))) t(e)
),
ID_key_value AS(
SELECT regexp_extract(ID, '(^|\D)(\d{10})(\D|$)', 2) AS ID,
       json_extract_scalar(e,'$.key') AS Key,
       json_extract_scalar(e,'$.value') AS Value
FROM key_value)
SELECT
  ID,
  kv['Name'] AS Name,
  kv['Email'] AS Email,
  kv['EId'] AS EId,
  kv['Company'] AS Company,
  kv['Groups'] AS Groups,
  kv['Address'] AS Address,
  kv['State'] AS State,
  kv['Component'] AS Component,
  kv['LastUsed'] AS LastUsed
FROM (
  SELECT ID, map_agg(Key, Value) kv
  FROM ID_key_value
  GROUP BY ID
)
;

Above query worked for me, first select unnests all JSON key value pairs into individual rows, second select just select the Key and Value as data and the third select pivots each ID into a single row based on Key value.