How i check condition in sub array?

Couchbase Server
1

I want to check condition in sub array like if employee id exists in survey_results it should not select that record.if i use unnest in survey_results it returns null if array is empty.

here is my query

SELECT survey.* FROM stitchit_initialization_hq survey UNNEST survey.branches br 
LEFT JOIN stitchit_initialization_hq employee ON employee.branch_id =  br AND employee.type='employee' 
WHERE survey.branch_type = 'Specific Branches' 
AND survey.type = 'survey_question' AND survey.created_date >= '2020-02-01' 
AND survey.created_date <= '2020-02-31' AND survey.survey_results.employee_id NOT IN  ['7019']

It should return records in which employee id in survey_results not found

{
  "survey_id": "322120000003",
  "survey_question": "Speceific",
  "option_1": "op1",
  "option_2": "op3",
  "option_3": "",
  "option_4": "",
  "option_5": "",
  "answer": "option_1",
  "branch_type": "Specific Branches",
  "branches": [
    "1231",
    "3221"
  ],
  "status": "Active",
  "branch_id": "3221",
  "created_date": "2020-02-10",
  "updated_date": "2020-02-10 23:15:14",
  "survey_results": [
    {
      "branch_id": "3221",
      "employee_id": "7019",
      "answer": "op1",
      "created_date": "2020-02-11"
    }
  ],
  "type": "survey_question"
}
2 
SELECT DISTINCT s.*
FROM stitchit_initialization_hq AS s
UNNEST s.branches AS br
LEFT JOIN stitchit_initialization_hq AS e ON e.type = "employee" AND br = e.branch_id
WHERE s.branch_type = "Specific Branches"
      AND s.type = "survey_question"
      AND s.created_date BETWEEN "2020-02-01" AND "2020-02-31"
      AND ANY sr IN s.survey_results SATISFIES sr.employee_id NOT IN ["7019"] END
3

Thank you so much :). The second left join is giving error when removing it . It works fine

SELECT DISTINCT s.*
FROM stitchit_initialization_hq AS s
UNNEST s.branches AS br
LEFT JOIN stitchit_initialization_hq AS e ON e.type = "employee" AND br = e.branch_id
WHERE s.branch_type = "Specific Branches" AND s.type = "survey_question"
AND s.created_date BETWEEN "2020-02-01" AND "2020-02-31"
AND ANY sr IN s.survey_results SATISFIES sr.employee_id IN ["7019"] END
4

Hi the above query works perfect if in survey_results has only 1 index with employee_id 7019 but when it has multiple array indexes like the second one it returns the doc . i want if employee 7019 found in the array it should not return this doc.

"survey_results": [
  {
    "answer": "Qatar",
    "branch_id": "3221",
    "created_date": "2020-02-11",
    "employee_id": "7019"
  },
],

its returning the doc when has multiple employee ids

"survey_results": [
  {
    "answer": "Qatar",
    "branch_id": "3221",
    "created_date": "2020-02-11",
    "employee_id": "7019"
  },
  {
    "answer": "Qatar",
    "branch_id": "3221",
    "created_date": "2020-02-11",
    "employee_id": "1305"
  }
],
5 
SELECT DISTINCT s.*
FROM stitchit_initialization_hq AS s
UNNEST s.branches AS br
LEFT JOIN stitchit_initialization_hq AS e ON e.type = "employee" AND br = e.branch_id
WHERE s.branch_type = "Specific Branches"
      AND s.type = "survey_question"
      AND s.created_date BETWEEN "2020-02-01" AND "2020-02-31"
      AND NOT (ANY sr IN s.survey_results SATISFIES sr.employee_id IN ["7019"] END)

OR

SELECT DISTINCT s.*
FROM stitchit_initialization_hq AS s
UNNEST s.branches AS br
LEFT JOIN stitchit_initialization_hq AS e ON e.type = "employee" AND br = e.branch_id
WHERE s.branch_type = "Specific Branches"
      AND s.type = "survey_question"
      AND s.created_date BETWEEN "2020-02-01" AND "2020-02-31"
      AND "7019" NOT IN s.survey_results[*].employee_id  ;

Also not sure why do u need UNNEST and LEFT JOIN because e never used in join or projection.

6

Thank you so much it works :slight_smile: i need unnest because i just want to show survey question to only those employees in specific branches given in branches array . i am doing unnest to join the branches of employee because each employee works in multiple branches.