Niql query search in sub array

Hi is there any way to search and get the records in one query instead of 2 quires . I wanna search to survey docs
and get the survey question if a person has not answered the question today. i wanna show survey question once in a day … to do this right now i can do this with two quires but i wanna to this in 1 query if possible.

MY QUERY 1 i am checking in this query that employee 7019 has answser the survey on today . if not answer i am searching for survey for employee 7019 which he has not answer yet in query 2

so i want these two queries in one query if possible thanks

QUERY ONE (checking if answer today)

SELECT survey.* FROM bk_initialization_hq survey WHERE
survey.type = ‘survey_question’
AND ((‘7019’ IN survey.survey_results[].employee_id AND ‘2020-04-19’ IN survey.survey_results[].created_date)

QUERY 2 if not answeer fetching the records

SELECT survey.* FROM bk_initialization_hq survey WHERE survey.branch_type = ‘All Branches’
AND survey.type = ‘survey_question’ AND survey.created_date BETWEEN ‘2020-04-01’ AND ‘2020-04-31’
AND (‘7019’ NOT IN survey.survey_results.employee_id
OR ARRAY_LENGTH(survey.survey_results) = 0) LIMIT 1

My docs
{
“survey_id”: “322120000065”,
“survey_question”: “Where is cadna”,
“option_1”: “op1”,
“option_2”: “op2”,
“option_3”: “op3”,
“option_4”: “”,
“option_5”: “”,
“answer”: “option_1”,
“branch_type”: “All Branches”,
“branches”: [
“3221”
],
“status”: “Active”,
“branch_id”: “3221”,
“created_date”: “2020-04-18”,
“updated_date”: “2020-04-18 21:13:50”,
“survey_results”: [
{
“branch_id”: “3221”,
“employee_id”: “7019”,
“answer”: “op2”,
“created_date”: “2020-04-19”
}
],
“type”: “survey_question”
}

here employee 7019 has answered so it should return null and should not search for any other survey

SELECT IFMISSINGORNULL(q1,q2).*
LET  q1 = (SELECT s.*
      FROM bk_initialization_hq s
      WHERE s.type = "survey_question"
            AND ANY r IN s.survey_results SATISFIES r.employee_id = "7019" AND r.created_date = "2020-04-19" END)[0],
q2 = (SELECT s1.*
      FROM bk_initialization_hq s1
      WHERE s1.type = "survey_question" AND s1.branch_type = "All Branches"
            AND s1.created_date BETWEEN "2020-04-01" AND "2020-04-31"
            AND ( ARRAY_LENGTH(s1.survey_results) = 0 OR ANY r IN s1.survey_results SATISFIES r.employee_id != "7019" END)
      LIMIT 1)[0]
;

[
{
“code”: 3000,
“msg”: “syntax error - at =”,
“query_from_user”: “SELECT IFMISSINGORNULL(q1,q2)\r\nq1 = (SELECT s.* FROM bk_initialization_hq s WHERE s.type = “survey_question” AND ANY r IN s.survey_results SATISFIES r.employee_id = “7019” AND r.created_date = “2020-04-19” END)[0],\r\nq2 = (SELECT s1.* FROM bk_initialization_hq s1 WHERE s1.type = “survey_question” AND s1.branch_type = “All Branches” AND s1.created_date BETWEEN “2020-04-01” AND “2020-04-31” AND ( ARRAY_LENGTH(s1.survey_results) = 0 || ANY r IN s1.survey_results SATISFIES r.employee_id != “7019” END) LIMIT 1)[0]”
}
]

updated previous post try again

Hi query is working but not scanning any index … I searched on N1QL index advisor but it is not recommending any …

here are the indexes i tried

CREATE INDEX def_created_date_survey_question ON bk_initialization_hq (created_date) WHERE (type = ‘survey_question’)

CREATE INDEX def_survey_results ON bk_initialization_hq (array_length((survey_results)))

"CREATE INDEX def_created_date_survey_results_branch_type_survey_question ON bk_initialization_hq (created_date,survey_results,branch_type) WHERE (type = ‘survey_question’)

Let
  • bindings
    • expr - ((select s.* from bk_initialization_hq as s where (((s.type) = “survey_question”) and any r in (s.survey_results) satisfies (((r.employee_id) = “7019”) and ((r.created_date) = “2020-04-19”)) end))[0])
    • var - q1
    • expr - ((select s1.* from bk_initialization_hq as s1 where (((((s1.type) = “survey_question”) and ((s1.branch_type) = “All Branches”)) and ((s1.created_date) between “2020-04-01” and “2020-04-31”)) and (array_length((s1.survey_results)) = (0 || any r in (s1.survey_results) satisfies (not ((r.employee_id) = “7019”)) end))) limit 1)[0])
    • var - q2

EXPLAIN/ADVISE will not show non from clause subqueries .

You do EXPLAIN/ADVISE on subquery separately.

CREATE INDEX ix1 ON  bk_initialization_hq(branch_type, created_date )  type = "survey_question";
CREATE INDEX ix2 ON  bk_initialization_hq(DISTINCT ARRAY v.employee_id FOR v IN  survey_results END)  type = "survey_question";
1 Like

HI the query is working fine but it returning the data even employee ‘7019’ has answered for the survey for ‘2020-04-18’ it should return null if he answered today because i want to show only 1 survey question per day if exists. if he not answered today it should return the a survey which he has not answered yet thanks.

SELECT IFMISSINGORNULL(q1,q2).*
LET q1 = (SELECT s.*
FROM bk_initialization_hq s
WHERE s.type = “survey_question”
AND ANY r IN s.survey_results SATISFIES r.employee_id = “7019” AND r.created_date = “2020-04-18” END)[0],
q2 = (SELECT s1.*
FROM bk_initialization_hq s1
WHERE s1.type = “survey_question” AND s1.branch_type = “All Branches”
AND s1.created_date BETWEEN “2020-04-01” AND “2020-04-31”
AND ( ARRAY_LENGTH(s1.survey_results) = 0 || ANY r IN s1.survey_results SATISFIES r.employee_id != “7019” END)
LIMIT 1)[0]

HERE THE RESULT

[
{
“answer”: “option_2”,
“branch_id”: “3221”,
“branch_type”: “All Branches”,
“branches”: [
“3221”
],
“created_date”: “2020-04-17”,
“option_1”: “ans 1”,
“option_2”: “ans 2”,
“option_3”: “ans 3”,
“option_4”: “ans 4”,
“option_5”: “ans 4”,
“status”: “Active”,
“survey_id”: “322120000009”,
“survey_question”: “Here is survey”,
“survey_results”: [
{
“answer”: “ans 1”,
“branch_id”: “3221”,
“created_date”: “2020-04-18”,
“employee_id”: “7019”
}
],
“type”: “survey_question”,
“updated_date”: “2020-04-17 11:46:19”
}
]
;

SELECT s.*
FROM bk_initialization_hq s
WHERE s.type = "survey_question"
      AND s.branch_type = "All Branches"
      AND s.created_date BETWEEN "2020-04-01" AND "2020-04-31"
      AND EVERY r IN s.survey_results
              SATISFIES r.employee_id != "7019" AND r.created_date != CLOCK_STR('1111-11-11') END
LIMIT 1;

OR

SELECT s1.*
FROM bk_initialization_hq s1
WHERE s1.type = "survey_question" AND s1.branch_type = "All Branches"
      AND s1.created_date BETWEEN "2020-04-01" AND "2020-04-31"
      AND ( ARRAY_LENGTH(s1.survey_results) = 0  OR ANY r IN s1.survey_results SATISFIES r.employee_id != "7019" END)
      AND NOT EXIST (SELECT RAW 1
                     FROM bk_initialization_hq s
                     WHERE s.type = "survey_question"
                            AND ANY r IN s.survey_results
                            SATISFIES r.employee_id = "7019" AND r.created_date = "2020-04-19" END)
LIMIT 1;

Perfect Thank you so much … The second solution seems easy to understand its AND NOT EXISTS i guess in the query and second thing the query works for me when i use OR instead of || .