get 'x-amz-copy-source' info from 'requestparameters'

Hello.

The error you're receiving, INVALID_FUNCTION_ARGUMENT: Invalid JSON path, indicates that there is an issue with the JSON path you've provided. In Athena, which is based on Presto, the json_extract_scalar function is used to extract a scalar value from a JSON object using the provided JSON path.

The problem may arise from the fact that the - character has special meaning in JSON paths. To access properties with - in their names, you often need to use double quotes around the property name.

Try modifying your JSON path for 'x-amz-copy-source' as follows:

json_extract_scalar(requestparameters, '$."x-amz-copy-source"') as source

Regards, Andrii

In some SQL engines, JSON paths need to be structured differently. If you're trying to access a JSON property in Redshift, the format should be something like json_extract_path_text(requestparameters, 'x-amz-copy-source'). Ensure the column containing the JSON data is indeed named 'requestparameters'.

Ensure that all rows in your requestparameters column are valid JSON. If even a single row contains invalid JSON, it could cause the entire query to fail. You can identify invalid JSON rows using a query like:

WHERE NOT is_valid_json(requestparameters)

Regards, Andrii

Sorry, it`s an answer to another question. Could you try this solution:

Instead of directly using json_extract_scalar, first, parse the JSON string using json_parse and then use the ->> operator to extract the value.

SELECT 
    userIdentity.username as username, 
    eventtime, 
    eventname, 
    awsregion, 
    sourceipaddress, 
    useragent, 
    requestparameters, 
    CAST(json_parse(requestparameters) ->> 'x-amz-copy-source' AS VARCHAR) as source,
    CAST(json_parse(requestparameters) ->> 'bucketName' AS VARCHAR) as bucketName, 
    CAST(json_parse(requestparameters) ->> 'key' AS VARCHAR) as targetObject 
FROM table 
WHERE eventName = 'CopyObject' 
ORDER BY eventtime desc;

This way, we first parse the JSON string into a JSON object, and then use the ->> operator to get the value corresponding to a specific key, and finally cast the result as VARCHAR (or whatever appropriate type). This should help bypass issues with json_extract_scalar.

Regards, Andrii