How to write a N1QL query to get all the SaleDt1 which has ordrqty equal to 102

/* 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"
       }
     }
    ]

Solution

Assume your document starts with ARRAY

SELECT dmfl.SaleDt1
FROM mybucket AS mb
UNNEST mb AS d
UNNEST d.`trans-data`.Mflnitmlst AS dmfl
WHERE ANY v IN dmfl.SaleDt1 SATISFIES v.ordrqty = "102" END;

If you want the object that has "102" only

SELECT sld.*
FROM mybucket AS mb
UNNEST mb AS d
UNNEST d.`trans-data`.Mflnitmlst AS dmfl
UNNEST dmfl.SaleDt1 AS sld
WHERE sld.ordrqty = "102";

If your document is not ARRAY it is object

SELECT sld.*
FROM mybucket AS mb
UNNEST mb.`trans-data`.Mflnitmlst AS dmfl
UNNEST dmfl.SaleDt1 AS sld
WHERE sld.ordrqty = "102";

Checkout https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

Update: With INSERT AND SELECT

INSERT INTO default VALUES ("f01", [ { "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" } } ]);

SELECT dmfl.SaleDt1
FROM default AS mb USE KEYS "f01"
UNNEST mb AS d
UNNEST d.`trans-data`.Mflnitmlst AS dmfl
WHERE ANY v IN dmfl.SaleDt1 SATISFIES v.ordrqty = "102" END;

If bucket name is trans-data and your sample document is from select query

SELECT dmfl.SaleDt1
FROM `trans-data` AS mb
UNNEST mb.Mflnitmlst AS dmfl
WHERE ANY v IN dmfl.SaleDt1 SATISFIES v.ordrqty = "102" END;