Athena SQL - Unable to UNNEST JSON string as desired

Question

I've JSON string in Athena as follows:

[{name=agreementUrl, value=agmt-id00001}, {name=sellerOfRecord, value=ABC Corporation}]
[{name=agreementUrl, value=agmt-id00002}, {name=sellerOfRecord, value=XYZ Corporation}]

I'm trying to get the values of Agreement ID in separate columns Agreement ID and sellerOfRecord. With the below query I was able to get these values out but these comes in separate rows. How can I get the agreement ID and corresponding vendor in the same result record?

SELECT
license_metadata.name,license_metadata.value
FROM
  json_licensedata
CROSS JOIN UNNEST(licensemetadata) t (license_metadata)

name	value
agreementUrl	agmt-id00001
sellerOfRecord	ABC Corporation
agreementUrl	agmt-id00002
sellerOfRecord	XYZ Corporation

Expected Output:

Agreement ID	sellerOfRecord
agmt-id00001	ABC Corporation
agmt-id00002	XYZ Corporation

Answer 1

You can apply the conditional aggregation on the dataset produced by your query :

SELECT MAX(CASE WHEN name = 'agreementUrl' THEN value END) as AgreementID,
       MAX(CASE WHEN name = 'sellerOfRecord' THEN value END) as sellerOfRecord
FROM (
  SELECT licensemetadata, license_metadata.name, license_metadata.value
  FROM json_licensedata
  CROSS JOIN UNNEST(licensemetadata) t (license_metadata)
) as s
GROUP BY licensemetadata