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.

asked 2 years ago1381 views
1 Answer
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
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions