How to handle conditional subquery's

SQL++
1

I am trying to build a dynamic Web Question form which is based on Questions which in turn can have related sub questions which are considered child. My Form Data is stored like this

{
	"_id" : "a53ae3bc-654c-4d9d-a6aa-62e2dd493f6e",
	"_type" : "dynform",
	"data" : [
		{"question" : "26da0986-0ec6-4701-842b-57bb26651689" , "order": 0 ,"child": []},
		{"question" : "36bc589a-b162-4c31-809d-2e0ad907480d" , "order" :2 ,"child": []},
		{"question" : "9f76b48f-962b-4f6f-b582-ed3955576b9c" , "order": 1, "child": [
			{"question" : "26da0986-0ec6-4701-842b-57bb26651754" , "order": 0 },
			{"question" : "26da0986-0ec6-4701-842b-57bb26647854" , "order": 1 }
			
		]},
		{"question" : "dd59dfa3-ec2e-4586-9984-b3143c279c2b" , "order" : 3, "child": []}
			]   
}

What i need is a way to get all data for the stored questions.

My current query to get the main level of questions looks like this

SELECT RAW (
    SELECT q.*
    FROM Contacts q
    WHERE _type = 'question'
        AND _id = d.question)
FROM Contacts c
UNNEST data d
WHERE c._type = 'dynform'
ORDER BY d.`order`
2

It is difficult order subquestions

If questions document key same as _id

SELECT
     (SELECT RAW q
      FROM Contacts AS q USE KEYS (ARRAY v.question FOR v WITHIN {d} WHEN v.question IS NOT MISSING END)
      WHERE _type = 'question') questions,
      d.`order`
FROM Contacts AS c
UNNEST c.data AS d
WHERE c._type = "dynform"
ORDER BY d.`order` ;

If questions document key not same as _id

SELECT
     (SELECT RAW q
      FROM Contacts AS q
      WHERE _type = 'question' AND _id IN (ARRAY v.question FOR v WITHIN {d} WHEN v.question IS NOT MISSING END)) questions,
      d.`order`
FROM Contacts AS c
UNNEST c.data AS d
WHERE c._type = "dynform"
ORDER BY d.`order` ;
3

When using the second option i get the following error

WHEN v.question NOT’, at: MISSING (reserved word)",

if i correct it and make it IS NOT Missing i get a new Error

  {
    "code": 3000,
    "msg": "syntax error - line 6, column 1, at: ARRAY (reserved word)",
    "query": "SELECT\r\n     (SELECT RAW q\r\n      FROM Contacts AS q USE KEYS (ARRAY v.question FOR v WITHIN {d} WHEN v.question IS NOT MISSING END)\r\n      WHERE _type = 'question') questions,\r\n      d.`order`\r\nARRAY v.question FOR v WITHIN {d} WHEN v.question IS NOT MISSING END\r\nFROM doc AS c\r\nUNNEST c.data AS d\r\nWHERE c._type = \"dynform\"\r\nORDER BY d.`order` ;"
  }

I could update the data to reflect the key if that will create a much better performance.

The USE KEY query also creates the same error

msg": “syntax error - line 3, column 88, near ’ WHEN v.question NOT’, at: MISSING (reserved word)”,

4

The following will be better

SELECT q AS question, d.`order`
FROM Contacts AS c
UNNEST (ARRAY {v.question, v.`order`} FOR v WITHIN c.data WHEN v.question IS NOT MISSING END) AS d
JOIN Contacts AS q ON d.question  = q._id AND q._type = "question"
WHERE c._type = "dynform"
ORDER BY d.`order` ;

Assume questions 3digit number fixed format like “001” and any child as level increases append “.001” (see sample document model (order field)) then you can get right order including childs

    {
            "_id": "a53ae3bc-654c-4d9d-a6aa-62e2dd493f6e",
            "_type": "dynform",
            "data": [
                {
                    "child": [],
                    "order": "000",
                    "question": "26da0986-0ec6-4701-842b-57bb26651689"
                },
                {
                    "child": [],
                    "order": "002",
                    "question": "36bc589a-b162-4c31-809d-2e0ad907480d"
                },
                {
                    "child": [
                        {
                            "order": "000.000",
                            "question": "26da0986-0ec6-4701-842b-57bb26651754"
                        },
                        {
                            "order": "000.001",
                            "question": "26da0986-0ec6-4701-842b-57bb26647854"
                        }
                    ],
                    "order": "001",
                    "question": "9f76b48f-962b-4f6f-b582-ed3955576b9c"
                },
                {
                    "child": [],
                    "order": "003",
                    "question": "dd59dfa3-ec2e-4586-9984-b3143c279c2b"
                }
            ]
        }

SELECT q AS question, d.`order`
FROM Contacts AS c
UNNEST (ARRAY {v.question, v.`order`} FOR v WITHIN c.data WHEN v.question IS NOT MISSING END) AS d
JOIN Contacts AS q ON d.question  = q._id AND q._type = "question"
WHERE c._type = "dynform"
ORDER BY d.`order` ;
5

Ok now i get all the questions but not in the way i want. i was hoping there is a way to go and get all the child items and return them in the array of the parent. in the children key. If that’s not posible i might have to implement and store the parent id in child and then write a script in js to merge the data.

From logic i was hoping to get all child elements for each question if there is a object in the child key, and then store it in a var and set the children key to this variable

6 
SELECT
     (SELECT RAW q
      FROM Contacts AS q
      WHERE q._type = "question" AND q._id = d.question)[0] AS question,
      child,
      d.`order`
FROM Contacts AS c
UNNEST c.data AS d
LET child = (WITH childqs AS (ARRAY v.question FOR v WITHIN d.child WHEN v.question IS NOT MISSING END)
              SELECT RAW q
              FROM Contacts AS q
              WHERE q._type = "question" AND q._id IN childqs)
WHERE c._type = "dynform"
ORDER BY d.`order` ;
7

I think we are getting closer but for some reason the LET child Query does not return any data, what would be the best approach to trouble shot this ?
To hopefully make it simpler i enclosed the sample data

SampleData.zip (761 Bytes)

8

Used default bucket

create index ix50 on default(_id) WHERE _type = "question";

UPSERT INTO default VALUES("a53ae3bc-654c-4d9d-a6aa-62e2dd493f6e",{ "_id": "a53ae3bc-654c-4d9d-a6aa-62e2dd493f6e", "_type": "dynform", "data": [ { "order": 0, "question": "26da0986-0ec6-4701-842b-57bb26651689" }, { "order": 2, "question": "36bc589a-b162-4c31-809d-2e0ad907480d" }, { "child": [ { "order": 0, "question": "26da0986-0ec6-4701-842b-57bb26651754" }, { "order": 1, "question": "26da0986-0ec6-4701-842b-57bb26647854" } ], "order": 1, "question": "9f76b48f-962b-4f6f-b582-ed3955576b9c" }, { "order": 3, "question": "dd59dfa3-ec2e-4586-9984-b3143c279c2b" } ] });
UPSERT INTO default VALUES("26da0986-0ec6-4701-842b-57bb26647854", { "_id": "26da0986-0ec6-4701-842b-57bb26647854", "_type": "question", "childhidekey": "", "childhidevalue": "", "children": [], "class": "", "controlType": "TextboxQuestion", "function": "", "key": "condition2", "label": "Conditional Q 2", "options": [], "order": 0, "placeholder": "Please provide Condition 2 Answer", "required": true, "type": "TextboxQuestion", "value": "" });
UPSERT INTO default VALUES("26da0986-0ec6-4701-842b-57bb26651689",{ "_id": "26da0986-0ec6-4701-842b-57bb26651689", "_type": "question", "childhidekey": "", "childhidevalue": "", "children": [], "class": "", "controlType": "TextboxQuestion", "function": "", "key": "phoneNumber", "label": "Phone Number", "options": [], "order": 0, "placeholder": "Please provide Phone Number", "required": true, "type": "TextboxQuestion", "value": "" });
UPSERT INTO default VALUES("26da0986-0ec6-4701-842b-57bb26651754",{ "_id": "26da0986-0ec6-4701-842b-57bb26651754", "_type": "question", "childhidekey": "", "childhidevalue": "", "children": [], "class": "", "controlType": "TextboxQuestion", "function": "", "key": "condition1", "label": "Conditional Q 1", "options": [], "order": 0, "placeholder": "Please provide Conditional Question", "required": true, "type": "TextboxQuestion", "value": "" });
UPSERT INTO default VALUES("36bc589a-b162-4c31-809d-2e0ad907480d",{ "_id": "36bc589a-b162-4c31-809d-2e0ad907480d", "_type": "question", "childhidekey": "", "childhidevalue": "", "children": [], "class": "", "controlType": "TextboxQuestion", "function": "", "key": "lastName", "label": "Last Name", "options": [], "order": 0, "placeholder": "Please provide Last Name", "required": true, "type": "TextboxQuestion", "value": "" });
UPSERT INTO default VALUES("9f76b48f-962b-4f6f-b582-ed3955576b9c",{ "_id": "9f76b48f-962b-4f6f-b582-ed3955576b9c", "_type": "question", "childhidekey": "", "childhidevalue": "", "children": [], "class": "", "controlType": "TextboxQuestion", "function": "", "key": "emailAddress", "label": "E-Mail Address", "options": [], "order": 0, "placeholder": "Please provide Email Address", "required": true, "type": "TextboxQuestion", "value": "" });
UPSERT INTO default VALUES("dd59dfa3-ec2e-4586-9984-b3143c279c2b",{ "_id": "dd59dfa3-ec2e-4586-9984-b3143c279c2b", "_type": "question", "childhidekey": "", "childhidevalue": "", "children": [], "class": "", "controlType": "TextboxQuestion", "function": "", "key": "firstName", "label": "First Name", "options": [], "order": 0, "placeholder": "Please provide First Name", "required": true, "type": "TextboxQuestion", "value": "" });

SELECT
     (SELECT RAW q
      FROM default AS q
      WHERE q._type = "question" AND q._id = d.question)[0] AS question,
      CASE WHEN ARRAY_LENGTH(childqs) > 0
           THEN (SELECT RAW q FROM default AS q WHERE q._type = "question" AND q._id IN childqs)
           ELSE MISSING
           END AS children,
      d.`order`
FROM default AS c
UNNEST c.data AS d
LET childqs = (ARRAY v.question FOR v WITHIN d.child WHEN v.question IS NOT MISSING END)
WHERE c._type = "dynform"
ORDER BY d.`order` ;
9

Ok that works much better with the sample, i still get error but i can figure out based on sample what causes it. Last 2 question is the children Array is there a way to set the children key inside the parent with the value of the subquery and not outside the parent as it is now ? Also how do i get rid of the question part in result and make it just a object with no name. The Children array is like that. Below is the output/ response which would be ideal

[
 {
      "_id": "9f76b48f-962b-4f6f-b582-ed3955576b9c",
      "_type": "question",
      "childhidekey": "",
      "childhidevalue": "",
      "children": [
      {
        "_id": "26da0986-0ec6-4701-842b-57bb26647854",
        "_type": "question",
        "childhidekey": "",
        "childhidevalue": "",
        "children": [],
        "class": "",
        "controlType": "TextboxQuestion",
        "function": "",
        "key": "condition2",
        "label": "Conditional Q 2",
        "options": [],
        "order": 0,
        "placeholder": "Please provide Condition 2 Answer",
        "required": true,
        "type": "TextboxQuestion",
        "value": ""
      },
      {
        "_id": "26da0986-0ec6-4701-842b-57bb26651754",
        "_type": "question",
        "childhidekey": "",
        "childhidevalue": "",
        "children": [],
        "class": "",
        "controlType": "TextboxQuestion",
        "function": "",
        "key": "condition1",
        "label": "Conditional Q 1",
        "options": [],
        "order": 0,
        "placeholder": "Please provide Conditional Question",
        "required": true,
        "type": "TextboxQuestion",
        "value": ""
      }
    ],
      "class": "",
      "controlType": "TextboxQuestion",
      "function": "",
      "key": "emailAddress",
      "label": "E-Mail Address",
      "options": [],
      "order": 0,
      "placeholder": "Please provide Email Address",
      "required": true,
      "type": "TextboxQuestion",
      "value": ""
    }
]
10 
SELECT
     (SELECT RAW q
      FROM default AS q
      WHERE q._type = "question" AND q._id = d.question)[0].* ,
      CASE WHEN ARRAY_LENGTH(childqs) > 0
           THEN (SELECT RAW q FROM default AS q WHERE q._type = "question" AND q._id IN childqs)
           ELSE MISSING
           END AS children,
      d.`order`
FROM default AS c
UNNEST c.data AS d
LET childqs = (ARRAY v.question FOR v WITHIN d.child WHEN v.question IS NOT MISSING END)
WHERE c._type = "dynform"
ORDER BY d.`order` ;