How to return all array values with few arrays being empty

Question

Below is the sample document from a bucket "Transaction"

[
  {
   "$chal": [
             "Dis.1234"
            ],
   "$DocVer":"1.0",
   "$Id":"45",
   "DNum":[],
  },
 {
   "$chal": [
             "Dis.46432"
            ],
   "$DocVer":"1.0",
   "$Id":"78",
   "DNum":[
           {
            "DnumValue":"87987"
           }
          ],
  }
]

Here i want to write a N1QL query to return Id and DN.DnumValue

But i'm getting only one result( Dnum with values) and Id and Dnum with empty array is not returned in the results. Can someone help here?

Here is the query i wrote

SELECT T.Id,DN.DnumValue FROM Transaction AS T UNNEST DNum AS DN WHERE T.$DocVer="1.0"

I see that using UNNEST function for the field with Array return only if there are values inside array. Here Id in a document with empty array for DNum is not returned in the result.

Answer 1

UNNEST is JOIN of document with ARRAY elements. If there is no row on one side it will reject other side. So UNNEST works same way.

You can use outer UNNEST i.e LEFT UNNEST

SELECT T.`$Id`, DN.DnumValue
FROM AS Transaction AS T
LEFT UNNEST T.DNum AS DN
WHERE T.`$DocVer`="1.0";