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.