Question 19065 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Athena UNNEST array into column values – Amazon Web Sevices

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
        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.


  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search