How to "move" a field to another document

query

#1

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 :wink:

Here is a mockup of the data model:

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"
}

User:Private:587CE5200641ABD9C1257E500051DDCD

{
  "country": "DK",
  "name": "John Dalsgaard",
  "type": "Private",
  "userkey": "587CE5200641ABD9C1257E500051DDCD",
  "zip": "4270"
}

It is the "anglerlicensenumber" that I want to move from the user document to the private document :slight_smile:


#2

You need to use two UPDATE statements

   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";

#3

Yep that kind of works…

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]);

#4

LET and LETTING only allowed in SELECT context.

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;

#5

Hi @vsr1

Thanks for your response. I’m learning from every suggestion :+1:

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…


#6
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;

#7

Hmmmm… it doesn’t work. It doesn’t mention any “Mutations” in the status (run time etc.) - so I guess it didn’t touch any documents?

And default is the bucket, right? So changing that to data (which is my bucket name) should be correct…?


#8

The following works.

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;

#9

Yes it works - I just had some wrong test data where there wasn’t a Private doc for the User doc that I tested with… :blush:

Thanks for the input - very educating! :+1: