Sub queries using USE KEYS

SQL++
1

I am storing all my Doc edits with the GUID of the user who did them. Thats all great but here comes the issue, The actual end user who is looking at the audit trail has no idea. Also as some backend is SQL the userid i use are basec UID without a prefix, but in couchbase i store them as user::GUID
So i use the blow query to get the user name based on the guid which works great

select referenceId,history.created_by,
(select name.fname || " " || name.lname as created_by_name from Contacts USE KEYS "user::" || c.history.created_by) from Contacts c
where _type="task" and referenceId = "contact::b15ca89b-8d14-4e83-8d7e-ed9bad71c773"

this returns me something like this

[
  {
    "$1": [
      {
        "created_by_name": "Tom Miller"
      }
    ],
    "created_by": "8D6D24A5-D669-45DC-99AC-F257BDA133A4",
    "referenceId": "contact::b15ca89b-8d14-4e83-8d7e-ed9bad71c773"
  }
]

but i would like to get something along the line like the below sample, is this possible ?

[
{
“created_by_name”: “Tom Miller”,
“created_by”: “8D6D24A5-D669-45DC-99AC-F257BDA133A4”,
“referenceId”: “contact::b15ca89b-8d14-4e83-8d7e-ed9bad71c773”
}
]

2 
select referenceId,history.created_by,
(select RAW name.fname || " " || name.lname  from Contacts USE KEYS "user::" || c.history.created_by)[0]   AS created_by_name from Contacts c
where _type="task" and referenceId = "contact::b15ca89b-8d14-4e83-8d7e-ed9bad71c773"
3

Ok that works, one more thing is there a way to put that raw output into a sub doc like history hat the output would look like this

{
    "assignedTo": null,
    "body": "Test task body",
    "categories": null,
    "completedDateTime": null,
    "createdDateTime": null,       
    "history": {
      "created_by": "CA58FBD2-9587-44CD-BA1D-8DAB895F6C89",
      "created_on": "2019-05-13T16:01:01.903Z",
      "created_by_name": "John Doe"
    },
    
    "subject": "Test task"
  }
4

{ “created_by”: expression , "created_on: expr…} AS history

5

Sorry maybe i was not clear, my doc stores it in the below format and returns it like that already

"history": {
      "created_by": "CA58FBD2-9587-44CD-BA1D-8DAB895F6C89",
      "created_on": "2019-05-13T16:01:01.903Z"
    },

what i am looking for is to be able to get the raw

(select RAW name.fname || " " || name.lname  from Contacts USE KEYS "user::" || c.history.created_by)[0]   AS created_by_name
  "created_by_name": "John Doe"

into the history Object

6 
select referenceId,
OBJECT_ADD(history, "created_by_name", (select RAW name.fname || " " || name.lname from Contacts USE KEYS "user::" || c.history.created_by)[0]) AS  history
 from Contacts c 
where _type="task" and referenceId = "contact::b15ca89b-8d14-4e83-8d7e-ed9bad71c773"
7

Thanks, that works but your solution has a typo so if someone looks it he might have issues . Thee should be a comma not a dot after the object

OBJECT_ADD(object, new_attr_key, new_attr_value)

8

So i got the OBJECT_ADD to work if i want to only add one Object to my DOC but what happens wen in my case i want 2 or more ? Like the name of the created and the name of the update ?

OBJECT_ADD(history, "created_by_name", (select RAW name.fname || " " || name.lname from Contacts USE KEYS "user::" || c.history.created_by)[0]) as history,
OBJECT_ADD(history, "updated_by_name", (select RAW name.fname || " " || name.lname from Contacts USE KEYS "user::" || c.history.updated_by)[0]) as history,

if i do that i get the error “msg”: “Duplicate result alias history.”, if i change history to another name it works again but then i have 2 subdocs each with one added object ?

Is there a way to push more then one Objects into doc ?

9

Use OBJECT_CONCAT() or do Cascade OBJECT_ADD(OBJECT_ADD(object, new_attr_key, new_attr_value), new_attr_key, new_attr_value) https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/objectfun.html

SELECT referenceId,
OBJECT_CONCAT(history,
      {"created_by_name": (SELECT RAW name.fname || " " || name.lname
                           FROM Contacts USE KEYS "user::" || c.history.created_by)[0],
       "updated_by_name": (SELECT RAW name.fname || " " || name.lname
                           FROM Contacts USE KEYS "user::" || c.history.updated_by)[0]
      }) AS history
FROM Contacts c
WHERE _type="task" AND referenceId = "contact::b15ca89b-8d14-4e83-8d7e-ed9bad71c773";