Extracting from Json in AWS Athena or Presto

Question

My query below does not give me any result

 WITH dataset AS (
    SELECT responseelements FROM cloudtrail_logs 
    WHERE useridentity.type = 'Root'
    AND eventname='CreateVpc'
    ORDER BY eventsource, eventname;
        AS blob
    )
    SELECT
      json_extract(blob, '$.vpc.vpcId') AS name,
      json_extract(blob, '$.ownerId') AS projects
    FROM dataset

But if I run only the inner query

SELECT responseelements FROM cloudtrail_logs 
WHERE useridentity.type = 'Root'
AND eventname='CreateVpc'
ORDER BY eventsource, eventname;

it gives me the correct response as a Json

{"requestId":"40aaffac-2c53-419e-a678-926decc48557","vpc":{"vpcId":"vpc-01eff2919c7c1da07","state":"pending","ownerId":"347612567792","cidrBlock":"10.0.0.0/26","cidrBlockAssociationSet":{"items":[{"cidrBlock":"10.0.0.0/26","associationId":"vpc-cidr-assoc-04136293a8ac73600","cidrBlockState":{"state":"associated"}}]},"ipv6CidrBlockAssociationSet":{},"dhcpOptionsId":"dopt-92df95e9","instanceTenancy":"default","tagSet":{},"isDefault":false}}

and if I pass this as data as below

WITH dataset AS (

SELECT '{"requestId":"40aaffac-2c53-419e-a678-926decc48557","vpc":{"vpcId":"vpc-01eff2919c7c1da07","state":"pending","ownerId":"347612567792","cidrBlock":"10.0.0.0/26","cidrBlockAssociationSet":{"items":[{"cidrBlock":"10.0.0.0/26","associationId":"vpc-cidr-assoc-04136293a8ac73600","cidrBlockState":{"state":"associated"}}]},"ipv6CidrBlockAssociationSet":{},"dhcpOptionsId":"dopt-92df95e9","instanceTenancy":"default","tagSet":{},"isDefault":false}}'

    AS blob
)
SELECT
  json_extract(blob, '$.vpc.vpcId') AS name,
  json_extract(blob, '$.ownerId') AS projects
FROM dataset

it gives me result , what I am missing here ? So that I am able to make it run in one shot Is it at all possible?

score 0 · Accepted Answer · answered Sep 21 '19 at 01:26

You're referencing the wrong column name in your query, it should be json_extract(responseelements, '$.vpc.vpcId') AS name instead of json_extract(blob, '$.vpc.vpcId') AS name. The AS blob part of this query does nothing since you can't alias an entire query, so take it out.

The AS blob works in your last example because you're selecting a value (the json string) into a column and the AS blob gives the column a name or alias of "blob". In your original query, you're selecting an existing column named responseelements so that's what you need to refer to in the json_extract function.

Extracting from Json in AWS Athena or Presto

1 Answers1