Selected fetching using n1ql

SQL++
1

I would like to fetch only the selected attributes from an array and selected attributes from another array inside the first one.

Below is the data sample:

{
  "id": "transCodes_HNLWS",
  "lastUpdBy": "1200",
  "version": "1.3",
  "docType": "transCodes",
  "cds": [
    {
      "mealCdPrmpt": false,
      "subTrans": [
        {
          "entryType": false,
          "alSubDept": 22,
          "prmptl": false,
          "descript": "Subdept description"
          "subDept": 1
        }
      ],
      "dept": 73,
      "descript": "Dept description",
    }
  ]
}

The data that I want would look like:

{
  "id": "transCodes_HNLWS",
  "cds": [
    {
      "dept": 73,
      "subTrans": [
        {
          "entryType": false,
          "subDept": 1
        }
      ]
    }
  ]
}

Please suggest :slight_smile:

2

By using ARRAY, OBJECT constructs you can get desired JSON model.

SELECT d.id,
      (ARRAY {cd.dept, "subTrans" : (ARRAY {st.entryType, st.subDept}  
                                      FOR st IN cd.subTrans 
                                      END) } 
       FOR cd IN d.cds 
       END) AS cds
FROM default AS d
WHERE ....;
1 Like
3

thank you vsr1. The N1QL worked exactly as desired.