How to loop nested documents and update the sub documents(n1ql query)

SQL++
,
1

I want to update particular question based on pagroupid, pageid, sectionid and question id, How to achieve using N1QL query

{
“id”:“f988c6f2-f422-494a-adbd-3c5ae94d752b”,
“pageGroups”:[
{
“pages”:[
{
“sections”:[
{
“questions”:[
{
“id”:“f988c6f2-f422-494a-adbd-3c5ae94d752a”,
“title”:"",
“subQuestions”:[

                       ]
                    }
                 ],
                 "id":"f988c6f2-f422-494a-adbd-3c5ae90d752c",
                 "title":"section 1"
              }
           ],
           "id":"f988c6f2-f422-494b-adbd-3c5de90d752a",
           "title":"page-1"
        }
     ],
     "id":"f988c6f2-f422-494b-adbd-3c5fe90d752a",
     "title":"page-Group-1"
  }

]
}

image
image797×423 16.3 KB

2 
UPDATE mybucket AS b
SET sq.setfield = "2021-07-26"
    FOR sq IN q.subQuestions
        FOR q IN s.questions
            FOR s IN p.sections
                FOR p IN pg.pages
                    FOR pg IN b.pageGroups
    WHEN pg.id = "........"
         AND p.id = "......."
         AND s.id = "......."
         AND q.id = "......."
         AND sq.id = "......."
    END,
    s.setfield = "2021-07-26"
            FOR s IN p.sections
                FOR p IN pg.pages
                    FOR pg IN b.pageGroups
    WHEN pg.id = "........"
         AND p.id = "......."
         AND s.id = "......."
    END
WHERE .........

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/update.html

3

Thanks vsr1, I have tried above n1ql query, and it’s executing without compile time issue, but it’s not updating the fields in the documents.

4

@RamadossE ,

INSERT INTO default VALUES ("f01",{"type":"doc", "id":"f988c6f2-f422-494a-adbd-3c5ae94d752b", "pageGroups":[ { "pages":[ { "sections":[ { "questions":[ { "id":"f988c6f2-f422-494a-adbd-3c5ae94d752a", "title":" questions 1", "subQuestions":[ { "id":"f988c6f2-f422-494a-adbd-3c5ae90d752d", "title":"subQuestions 1" } ] } ], "id":"f988c6f2-f422-494a-adbd-3c5ae90d752c", "title":"section 1" } ], "id":"f988c6f2-f422-494b-adbd-3c5de90d752a", "title":"page-1" } ], "id":"f988c6f2-f422-494b-adbd-3c5fe90d752a", "title":"page-Group-1" } ] });

UPDATE default AS b USE KEYS "f01"
SET sq.setfield = "2021-07-26"
    FOR sq IN q.subQuestions
        FOR q IN s.questions
            FOR s IN p.sections
                FOR p IN pg.pages
                    FOR pg IN b.pageGroups
    WHEN pg.id = "f988c6f2-f422-494b-adbd-3c5fe90d752a"
         AND p.id = "f988c6f2-f422-494b-adbd-3c5de90d752a"
         AND s.id = "f988c6f2-f422-494a-adbd-3c5ae90d752c"
         AND q.id = "f988c6f2-f422-494a-adbd-3c5ae94d752a"
         AND sq.id = "f988c6f2-f422-494a-adbd-3c5ae90d752d"
    END
WHERE b.type = "doc";

Conditions in WHEN optional if you want update every thing.

OR

If you want update matching id any where in the document use WITHIN

UPDATE default AS b USE KEYS "f01"
SET v.setfield = "2021-07-26"
    FOR v WITHIN b
    WHEN v.id = "f988c6f2-f422-494a-adbd-3c5ae90d752d"
    END
WHERE b.type = "doc";
1 Like
5

Thanks vsr1, it’s working as expected.