N1QL query to rename existing filed and add a new field

SQL++
1

Bucket: default

I have below available PROFILE documents

{
"id":1001,
"country":"US",
"doc_type":"PROFILE"
}

{
"id":1002,
"country":"UK",
"doc_type":"PROFILE"
}

{
"id":1003,
"country":"SL",
"doc_type":"PROFILE"
}

{
"id":1004,
"country":"US",
"doc_type":"PROFILE"
}

I have below PREFERENCE documents

{
"id":1001,
"sd":"I",
"sd_dtm":"2020",
"doc_type":"PREFERENCE"
}
{
"id":1002,
"sd":"O",
"sd_dtm":"2021",
"doc_type":"PREFERENCE"
}
{
"id":1003,
"sd":"",
"doc_type":"PREFERENCE"
}
{
"id":1004,
"sd":"",
"doc_type":"PREFERENCE"
}

Please help me with N1QL query to update PREFERENCE documents as below

  • Rename field “sd” to “gl”
  • If “sd_dtm” exists, rename “sd_dtm” to “gl_dtm”
  • Set gl="" for id in (select id from bucket where country != “US” and doc_type=“PROFILE”)
  • Set “tp”=“gl”
  • if “gl_dtm” exists, set “tp_dtm”=“gl_dtm”

Somewhat I was trying as below: -

  1. Below query will set values of gl,gl_dtm, tp, tp_dtm from sd and sd_dtm respectively for US based profiles and delete sd and sd_dtm.

UPDATE default SET gl = sd, gl_dtm = sd_dtm, tp = sd, tp_dtm = sd_dtm UNSET sd, sd_dtm where doc_type=“PREFERENCE” and sd is NOT MISSING and id in (select id from default where doc_type=‘PROFILE’ and country = “US”)

  1. Once above is executed, the below query will set gl and tp = “” for all Non-US profiles

UPDATE default set gl="", tp="" UNSET sd, sd_dtm where doc_type=“PREFERENCE” and sd is NOT MISSING

Please help

Thank you,
Regards,
jayant

2 
UPDATE default AS d
SET d.gl = IFMISSING(d.sd, d.gl), d.gl_dtm = IFMISSING(d.sd_dtm, d.gl_dtm)
UNNESET d.sd, d.gl_dtm
WHERE d.doc_type = "PREFERENCE" AND IFMISSING(d.sd, d.sd_dtm) IS NOT MISSING AND d.country = "US";