Conditional SubQuery

SQL++
1

What is the best way to achieve the following , i have a query which gets all followups for an user which returns me data like this in my N1QL result.

{
“DocId”: “followup::8b146d36-831c-4a2b-a839-4ffab4a2d11e”,
“body”: “Test Followup”,
“created_by”: “8D6D24A5-D669-45DC-99AC-F257BDA133A4”,
“created_by_name”: “Tom Miller”,
“created_on”: “2019-05-21T18:58:59.938Z”,
“dueDateTime”: “2019-05-07T07:00:00.000Z”,
“method”: “Mail”,
“owner”: “8D6D24A5-D669-45DC-99AC-F257BDA133A4”,
“owner_name”: “Tom Miller”,
“parent_id”: “farm::4670542B-4A4B-4D5A-B3A6-BCA7715E8276”,
“reminder”: true,
“reminderDateTime”: null,
“startDateTime”: “2019-05-01T07:00:00.000Z”
}

To be able to get the Info on wh the followup is for i need to lookup the parent_id which is the key of the Entity to followup with. In this case its a farm, but it could also be a contact, a customer or a lead. Problem is that each store the info differently. So for example if its a farm i want to get the OwnerFirstName and OwnerLastName, if its a Contact i want to get Firstname and LastName and this sould be decided based on whats left of the :: in dockKey like farm, contact etc. How can i build that logic into a sub query ?

2 
SELECT (CASE  WHEN prefix = "farm" THEN {"fname":p.OwnerFirstName, "lname": p.OwnerLastName} 
             WHEN prefix = "contact"  TEHN {"fname": p.FirstName, "lname":p.LastName} 
             ELSE {} END).*, p.address
FROM (Your query) AS d JOIN default AS  p ON KEYS d.parent_id 
LET prefix =  SPLIT(d.parent_id)[0];
3

So a join is preferred of a conditional sub query ?

4

It is not. In your case you need to get other document so used JOIN.

5

Ok i modified my query based on your info and it works but i need the output a bit different. Query looks like this now

Select 
   meta(c).id as DocId,
   c.body,
   c.method,
   c.startDateTime,
   c.dueDateTime,
   c.owner,
   (SELECT RAW name.fname || " " || name.lname FROM Contacts USE KEYS "user::" || c.owner)[0] as owner_name,
   c.parent_id,
   prefix,
   c.reminder,
   c.reminderDateTime,
   c.history.created_by as created_by,
   c.history.created_on as created_on,
   (SELECT RAW name.fname || " " || name.lname FROM Contacts USE KEYS "user::" || c.history.created_by)[0] as created_by_name,
   c.history.updated_by as updated_by,
   c.history.updated_on as updated_on,
   (SELECT RAW name.fname || " " || name.lname FROM Contacts USE KEYS "user::" || c.history.updated_by)[0] as updated_by_name,
   (CASE  WHEN prefix = "farm" THEN {"fname":p.Owners.owner1FName, "lname": p.Owners.owner1LName} 
          WHEN prefix = "contact"  THEN {"fname": p.first_name, "lname":p.last_name} 
             ELSE {} END)
     
   from Contacts AS c JOIN Contacts AS p ON KEYS c.parent_id 
   LET prefix =  SPLIT(c.parent_id, "::")[0]
   where c._type="followup" and c.owner = '8D6D24A5-D669-45DC-99AC-F257BDA133A4'

But the result from join looks like this…

{
“$1”: {
“fname”: “”,
“lname”: “Planned Community Assn”
},
“DocId”: “followup::8b146d36-831c-4a2b-a839-4ffab4a2d11e”,
“body”: “Test notes”,
“created_by”: “8D6D24A5-D669-45DC-99AC-F257BDA133A4”,

So how can i get rid of the $1 and make fname and lname part of the main doc ?

6

Use dot STAR

(CASE …).*

7

Perfect that did the job

8

@aponnath , As you already use subquery you can use like this to avoid join

SELECT
   META(c).id AS DocId,
   c.body,
   c.method,
   c.startDateTime,
   c.dueDateTime,
   c.owner,
   (SELECT RAW name.fname || " " || name.lname FROM Contacts USE KEYS "user::" || c.owner)[0] as owner_name,
   c.parent_id,
   prefix,
   c.reminder,
   c.reminderDateTime,
   c.history.created_by as created_by,
   c.history.created_on as created_on,
   (SELECT RAW name.fname || " " || name.lname FROM Contacts USE KEYS "user::" || c.history.created_by)[0] as created_by_name,
   c.history.updated_by as updated_by,
   c.history.updated_on as updated_on,
   (SELECT RAW name.fname || " " || name.lname FROM Contacts USE KEYS "user::" || c.history.updated_by)[0] as updated_by_name,
   (SELECT RAW (CASE  WHEN prefix = "farm" THEN {"fname":p.Owners.owner1FName, "lname": p.Owners.owner1LName}
             WHEN prefix = "contact"  THEN {"fname": p.first_name, "lname":p.last_name}
             ELSE {} END)
    FROM Contacts AS p USE KEYS c.parent_id LET prefix = SPLIT(c.parent_id, "::")[0])[0].*
   FROM Contacts AS c
   where c._type="followup" and c.owner = '8D6D24A5-D669-45DC-99AC-F257BDA133A4'