sqlamazon-web-servicesamazon-s3amazon-s3-select

S3 Select Invalid Path component


I'm trying to figure out how to use AWS S3 Select, everything seems pretty straight forward, but the following query just doesn't want to work:
select r.value from S3Object[*].outputs.private_subnets r
the above returns Invalid Path component.

This is the JSON I'm working with:

{
    "outputs": {
        "private_subnets": {
            "value": [
                "subnet-1",
                "subnet-2",
                "subnet-3"
            ],
            "type": [
                "tuple",
                [
                    "string",
                    "string",
                    "string"
                ]
            ]
        },
        "public_subnets": {
            "value": [
                "subnet-1",
                "subnet-2",
                "subnet-3"
            ],
            "type": [
                "tuple",
                [
                    "string",
                    "string",
                    "string"
                ]
            ]
        },
        "vpc_id": {
            "value": "vpc-123",
            "type": "string"
        }
    }
}

I just don't understand the error, is value a special word in SQL?
Here's what I've tried so far:
select r from S3Object[*].outputs.private_subnets r:

{
    "r": {
        "value": [
            "subnet-1",
            "subnet-2",
            "subnet-3"
        ],
        "type": [
            "tuple",
            [
                "string",
                "string",
                "string"
            ]
        ]
    }
}

select r.type from S3Object[*].outputs.private_subnets r:

{
    "type": [
        "tuple",
        [
            "string",
            "string",
            "string"
        ]
    ]
}

So I just don't understand what's the problem with value.


Solution

  • You are unable to select this because "value" is a reserved keyword. https://docs.aws.amazon.com/AmazonS3/latest/dev/s3-glacier-select-sql-reference-keyword-list.html . This can be escaped using double quotes around the value.