(resolved) Redshift Spectrum: Not able to query struct data in parquet file stored in s3

Question

[updated]
Resolved with this [approach](https://repost.aws/questions/QUmj-OVNL1RxGVCT1SrWyQFw/redshift-spectrum-not-able-to-query-struct-data-in-parquet-file-stored-in-s3#COz1D3R_csS-iLeagxxkPyAQ).

Hey there,

I have a struct column called **members** in parquet stored in S3, the column structure is like this:
```
{
  "members": {
    "member0": {
      "timeFrames": [
        {
          "id": "string",
          "startAt": "string",
          "endAt": "string"
        }
      ],
      "bufferTime": "bigint"
    },
    "member1": {
      "timeFrames": [
        {
          "id": "string",
          "startAt": "string",
          "endAt": "string"
        }
      ],
      "bufferTime": "bigint"
    }
  }
}
```
And I tried to untested it with following query:

```
SELECT t.members.member0
FROM my_schema.my_table AS t
```

but received the following error 
```
Struct type "t.members.member0" cannot be accessed directly.
Hint: Use dot notation to access specific attributes of the struct.
```
According to documentation, it should work with struct data and I couldn't resolve it further.

Answer

Hello there,

The key is to use dot notation to access specific attributes of the struct in order to retrieve your individual values.

In your case, instead of using the syntax t.members.member0, you should use dot notation to access the specific attributes within the struct. Here's an example of how you can modify your query:

```
SELECT t.members.member0.timeFrames[1].id AS id,
       t.members.member0.timeFrames[1].startAt AS startAt,
       t.members.member0.timeFrames[1].endAt AS endAt,
       t.members.member0.bufferTime AS bufferTime
FROM my_schema.my_table AS t;
```

Make sure to replace [1] with the appropriate index based on your data structure. The key is to use dot notation to navigate through the nested structure and access the desired values.

For further reference, you may want to take a look at [this Redshift doc](https://docs.aws.amazon.com/redshift/latest/dg/tutorial-query-nested-data.html#tutorial-query-nested-data-sqlextensions) to Query your nested data in S3.

Hope this helps!

(resolved) Redshift Spectrum: Not able to query struct data in parquet file stored in s3

相关内容