How to select field from a sibling array?

Question

I would like to select value ('Value') from element with FieldName=Field4 where value ('Value') is null from element with FieldName=Field2.

{
    "FormName":"Form1",
    "Id": "ID1",
    "Module":{
       "ModuleType":"Form",
       "Layout":"Vertical",
       "ControlList":[
          {
             "ControlType":"Widget",
             "Layout":"Vertical",
             "FieldList":[
                {
                   "FieldType":"TextBox",
                   "FieldName":"Field1",
                   "Value":"field1"
                },
                {
                   "FieldType":"TextBox",
                   "FieldName":"Field2",
                   "Value":null
                },
                {
                   "FieldType":"TextBox",
                   "FieldName":"Field1",
                   "Value":"field3"
                }
             ]
          },
          {
             "ControlType":"Widget",
             "Layout":"Vertical",
             "FieldList":[
                {
                   "row":[
                      {
                         "FieldType":"TextBox",
                         "FieldName":"Field3",
                         "Value":"field3"
                      },
                      {
                         "FieldType":"TextBox",
                         "FieldName":"Field4",
                         "Value":"field4"
                      }
                   ]
                }
             ]
          }
       ]
    }
 }

I understand how to get the right dataset:

select
 form.Id
from
 `test` as form
where
 any cl in form.Module.ControlList satisfies 
   any fl in cl.FieldList satisfies fl.FieldName = 'Field2' AND fl.`Value` is null
   end 
 end;

... returning:

[
  {
    "Id": "ID1"
  }
]

But how should my select statement look like to get this:

[
  {
    "Value": "field4"
  }
]

Answer 1

Use ARRAY construct

SELECT
 ARRAY_FLATTEN((ARRAY (ARRAY (ARRAY {r.`Value`}
               FOR r IN f.`row`
               WHEN r.FieldName = "Field4"
               END)
        FOR f IN c.FieldList
        END)
 FOR c IN t.Module.ControlList
 END),3)[0].*
FROM `test` AS t
WHERE (ANY cl IN t.Module.ControlList
       SATISFIES (ANY fl IN cl.FieldList
                  SATISFIES fl.FieldName = 'Field2' AND fl.`Value` IS NULL
                  END)
       END);