I have a data model where I have a User document with a key like: User:1234 and for confident/personal data I have created a separate document with a key like: User:Private:1234 - so I can easily find the document with the private information if I have the user document.
But now I have identified another field that really also should be private. So can I build a N1QL query to “move” that field from my user document to the corresponding private document? Instead of just writing some code to do it
UPDATE default AS d
SET d.anglerlicensenumber = (SELECT RAW d1.anglerlicensenumber
FROM default AS d1 USE KEYS "User:"||d.userkey
WHERE d1.type = "User")[0]
WHERE d.type = "Private";
UPDATE default AS d
UNSET d.anglerlicensenumber
WHERE d.type = "User";
However, some of the User documents don’t have that field - and therefore the field is added with a null value. So I tried to use some of my former experience from Select’s to calculate the value - and only update if it has one. But these are not valid syntax - so not sure if that can be done…
First try;
UPDATE data AS d
LET an = (SELECT RAW d1.anglerlicensenumber
FROM data AS d1 USE KEYS "User:"||d.userkey
WHERE d1.type = "User")[0])
SET d.anglerlicensenumber = an
WHERE d.type = "Private" and an is valued;
Second try;
UPDATE data AS d
SET d.anglerlicensenumber = an
WHERE d.type = "Private" and an is valued
LETTING an = (SELECT RAW d1.anglerlicensenumber
FROM data AS d1 USE KEYS "User:"||d.userkey
WHERE d1.type = "User")[0]);
In that case remove RAW in SELECT and select field outside (ARRAY can’t have scalar MISSING due to position shift, so it used NULL. By removing RAW it makes ARRAY of empty object. On empty object anglerlicensenumber gives MISSING and that will set during update )
UPDATE default AS d
SET d.anglerlicensenumber = (SELECT d1.anglerlicensenumber
FROM default AS d1 USE KEYS "User:"||d.userkey
WHERE d1.type = "User")[0].anglerlicensenumber
WHERE d.type = "Private";
Also you can use MERGE
MERGE INTO default AS m
USING (SELECT "User:Private:" || u.`key` AS id, u.anglerlicensenumber
FROM default AS u
WHERE u.type = "User" AND u.anglerlicensenumber IS NOT NULL) AS o
ON KEY o.id
WHEN MATCHED THEN
UPDATE
SET m.anglerlicensenumber = o.anglerlicensenumber;
Thanks for your response. I’m learning from every suggestion
The first suggestion does not work. It says it mutates all Private docs. But it does not fill in the field for any of them. If I force a fixed user key into the statement then it works (i.e. it copies that number to all private docs.).
UPDATE data AS d
SET d.anglerlicensenumber = (SELECT d1.anglerlicensenumber
FROM data AS d1 USE KEYS "User:02330E2E78D4A1CFC1257F39004A8B72"
WHERE d1.type = "User")[0].anglerlicensenumber
WHERE d.type = "Private";
Not sure why appending d.userkey to “User:” does not work. It looks Ok to me…
The MERGE suggestion seems very accurate in the conceptual understanding of what is needed: “Find all users with an angler licence number, and for those create a similar field in the corresponding private document with the same value”…
However, there is a syntax error:
MERGE INTO data
USING (SELECT "User:Private:" || u.`key` AS id, u.anglerlicensenumber
FROM data AS u
WHERE u.type = "User" AND u.anglerlicensenumber IS NOT NULL) AS o
ON KEY o.id
WHEN MATCHED THEN
UPDATE data AS up <-- error : 3000 - syntax error - at data
SET up.anglerlicensenumber = o.anglerlicensenumber;
Have tried various things - but I have not been able to find a solution for that…
MERGE INTO default AS m
USING (SELECT "User:Private:" || u.`key` AS id, u.anglerlicensenumber
FROM default AS u
WHERE u.type = "User" AND u.anglerlicensenumber IS NOT NULL) AS o
ON KEY o.id
WHEN MATCHED THEN
UPDATE SET m.anglerlicensenumber = o.anglerlicensenumber;
INSERT INTO default VALUES("User:587CE5200641ABD9C1257E500051DDCD", { "acceptconditions": true, "acceptconditionsdate": "2018-08-28T10:47:03+0200", "anglerlicensenumber": "1234567", "key": "587CE5200641ABD9C1257E500051DDCD", "lastloginservice": "2018-10-31T14:33:50+0100", "lastloginweb": "2018-11-29T10:08:25+0100", "type": "User" } );
INSERT INTO default VALUES("User:Private:587CE5200641ABD9C1257E500051DDCD", { "country": "DK", "name": "John Dalsgaard", "type": "Private", "userkey": "587CE5200641ABD9C1257E500051DDCD", "zip": "4270" });
MERGE INTO default AS m
USING (SELECT "User:Private:" || u.`key` AS id, u.anglerlicensenumber
FROM default AS u
WHERE u.type = "User" AND u.anglerlicensenumber IS NOT NULL) AS o
ON KEY o.id
WHEN MATCHED THEN
UPDATE SET m.anglerlicensenumber = o.anglerlicensenumber;
