Questions for querying from spectrum data stored STRUCT-STRUCT-ARRAY data type.

0

Questions for querying from spectrum data stored STRUCT-STRUCT-ARRAY data type. There is a table "psn_member_chnl" in spectrum with multi level STRUCT type data such as

struct<publish_info:
    struct<category:
         struct<svc_genre_id_list:array[svc_genre_id:bigint]>
>
>

I run a query from psn_member_chnl like bellow:

select a.member_chnl_id, a.character_no, a.eval_info.eval_status_type
  , b.track_id
  , c.svc_genre_id
from rs_flo_poc.psn_member_chnl a, a.track_list b, a.publish_info.category.svc_genre_id_list c

Then error comes up as bellow:

SQL Error [XX000]: ERROR: "c.svc_genre_id" is of scalar type BIGINT. No further nested access is permitted.

Please give me any hint to solve this error.

已提問 2 年前檢視次數 1438 次
1 個回答
0

STRUCT is accessed via join and ARRAY is accessed via dot notation.

Kindly refer to this lab which demonstrates with JSON samples https://catalog.us-east-1.prod.workshops.aws/workshops/9f29cdba-66c0-445e-8cbb-28a092cb5ba7/en-US/lab8

Try using something on the lines of below

select
	c.svc_genre_id
from 
	pmc.publish_info pi,
	pi.category c,
	c.svc_genre_id_list c
profile pictureAWS
已回答 2 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南