Verify nested field

SQL++
,
1

can I use where exists to fetch a document which has a deptn (say d1) and subdeptn (say sd1) and which is active (“Y”)?

I’ve recently migrated to Couch from Oracle and I find this a bit intimidating.

My document is as follows:

{
  "docType": "transCodes",
  "cds": 
  [
    {
      "deptn": 73,
      "subTrans": 
	  [
        {
          "subDept": 1,
		  "active": "Y",
          "prmpts": 
		  [
            {
              "inclSubDept": 99,
              "inclDept": 96
            }
          ]
		}
	  ]
	}
  ]
}

really appreciate your help :slight_smile:

2 
SELECT d.*
FROM default AS d
WHERE d.docType =  "transCodes"
       AND  ANY cd IN d.cds SATISFIES cd.deptn = 73 
                     AND  (ANY st IN cd.subTrans SATISIFIES st.subDept = 1 AND st.active = "Y" END) 
         END

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

https://query-tutorial.couchbase.com/tutorial/#55