I have a JSON value
<code>{
"test": {
"userId": 77777,
"sectionScores": [
{
"id": 2,
"score": 244,
},
{
"id": 1,
"score": 212
}
]
}
}
</code>
<code>{
"test": {
"userId": 77777,
"sectionScores": [
{
"id": 2,
"score": 244,
},
{
"id": 1,
"score": 212
}
]
}
}
</code>
{
"test": {
"userId": 77777,
"sectionScores": [
{
"id": 2,
"score": 244,
},
{
"id": 1,
"score": 212
}
]
}
}
NOTE : the order of sectionScores vary.
id 1 represent sectionname1 and id 2 represent sectionname2 and id 3 represent sectionname3
i have a redshift table
<code>create table stage.poc1(
user_id bigint ,
json_data super
)
</code>
<code>create table stage.poc1(
user_id bigint ,
json_data super
)
</code>
create table stage.poc1(
user_id bigint ,
json_data super
)
from where i write the flatten query and insert into the target table
select user_id, json_data.test[0] … I dont know how to write
<code>create table target.poc
{
user_id bigint,
sectionname1_score int,
sectionname2_score int,
sectionname3_score int,
}
</code>
<code>create table target.poc
{
user_id bigint,
sectionname1_score int,
sectionname2_score int,
sectionname3_score int,
}
</code>
create table target.poc
{
user_id bigint,
sectionname1_score int,
sectionname2_score int,
sectionname3_score int,
}
insert into
select user_id, json_data.test[0].score as sectionname1_score … I dont know how to write