How to use ARRAY function without using UNNEST when there is an ARRAY which is also inside an another ARRAY?

Welcome to Programming Tutorial official website. Today - we are going to cover how to solve / find the solution of this error How to use ARRAY function without using UNNEST when there is an ARRAY which is also inside an another ARRAY? on this date .

Below is the sample document

[
     {
    "trans-data": 
        { 
       "$Docver":"1.0",
        "ManufId":"1234543",
        "ToyDot": 
                 {
                   "GrossAmt":"675",
                   "Wqty":"200"
                  },
       "Mflnitmlst": 
                   [
                      {
                        "Mfprcdv":"25000",
                        "SaleDt1":
                          [
                            {
                              "Mtid":"0987655",
                              "ordrqty":"102",
                              "Plainqty":"1000"
                             }
                          ]
                       },
                      {
                        "Mfprcdv":"25000",
                        "SaleDt1":
                          [
                            {
                              "Mtid":"0987656",
                              "ordrqty":"110",
                              "Plainqty":"1500"
                             }
                          ]
                      },
                     {
                        "Mfprcdv":"25000",
                        "SaleDt1":
                          [
                            {
                              "Mtid":"0987657",
                              "ordrqty":"120",
                              "Plainqty":"2000"
                             }
                          ]
                      }
                   ],
        "Tmstp":"2021-03-04T14:23:21",
        "Sectn":"XYZ"
       }
     },
    {
    "trans-data": 
        {
        "$Docver":"1.0",
        "ManufId":"1234543",
        "ToyDot": 
                 {
                   "GrossAmt":"605",
                   "Wqty":"100"
                  },
        "Mflnitmlst": 
                   [
                      {
                        "Mfprcdv":"26000",
                        "SaleDt1":
                          [
                            {
                              "Mtid":"1987655",
                              "ordrqty":"102",
                              "Plainqty":"1000"
                             }
                          ]
                       },
                      {
                        "Mfprcdv":"26000",
                        "SaleDt1":
                          [
                            {
                              "Mtid":"1987656",
                              "ordrqty":"110",
                              "Plainqty":"1500"
                             }
                          ]
                      },
                     {
                        "Mfprcdv":"26000",
                        "SaleDt1":
                          [
                            {
                              "Mtid":"1987657",
                              "ordrqty":"120",
                              "Plainqty":"2000"
                             }
                          ]
                      }
                   ],
        "Tmstp":"2021-03-04T14:23:21",
        "Sectn":"XYZ"
       }
     }
    ]

I wrote the below N1QL query and got the below results

QUERY:

SELECT DISTINCT ARRAY {V.ordrqty,V.Plainqty} FOR V IN M.SaleDt1 WHEN V.ordrqty IN [120] END
FROM `trans-data` AS T
UNNEST Mflnitmlst as M
WHERE t.Sectn="XYZ"

RESULT:

[{
    "$1": []
  },
  {
    "$1": [{
      "ordrqty": 120,
      "Plainqty": "2000"
    }]
  },
  {}
]

Now I want to query without using UNNEST function. Can someone help here?

Answer

Construct new array based on your conditions

SELECT  ARRAY_DISTINCT(ARRAY_FLATTEN(ARRAY (ARRAY {s.ordrqty,s.Plainqty}
                                            FOR s IN m.SaleDt1
                                            WHEN s.ordrqty IN [120]
                                            END)
                                     FOR m IN t.Mflnitmlst
                                     END, 1) ) AS orders
FROM `trans-data` AS t
WHERE t.Sectn="XYZ"
      AND (ANY m IN t.Mflnitmlst
           SATISFIES (ANY s IN m.SaleDt1
                     SATISFIES s.ordrqty IN [120]
                     END)
           END);