Need N1ql query to Unnest array object inside array object?

SQL++
1

Hi,

Below is my json:
Bucket is default.

"info": [
  {
    "action": "A",
    "doc": {
      "aud_info": {
        "action": "A",
        "comment": "Some comment",
        "ts": "2017-08-21T19:45:16",
        "upd_by": "DB ADMIN",
        "version": "2"
      },
      "dtype": "prod",
      "fund_in": "AC",
      "fund_out": "100",
      "fund_out_mn": "MM",
      "product": {
        "code": "AC100L0",
        "name": "Product344"
      },
      "segment": "L0"
    },
    "id": "prod::AC+100+L0"
  },
  {
    "action": "A",
    "doc": {
      "aud_info": {
        "action": "A",
        "comment": "Some comment",
        "ts": "2017-08-21T19:45:15",
        "upd_by": "DB ADMIN",
        "version": "1"
      },
      "dtype": "prod",
      "fund_in": "CC",
      "fund_out": "000",
      "fund_out_mn": "MM",
      "product": {
        "code": "AC000L0",
        "name": "Product45325"
      },
      "segment": "L1"
    },
    "id": "prod::CC+000+L1"
  }
]

}
]

How to write N1ql to get code and name from product field.

2

SELECT ud.doc.product FROM default d UNNEST d.info AS ud;

You can also add predicate

SELECT ud.doc.product FROM default d UNNEST d.info AS ud WHERE ud.id = "prod::CC+000+L1";

Checkout Nesting and Unnesting by @johan_larson http://blog.couchbase.com/wp-content/uploads/2017/03/N1QL-A-Practical-Guide-v2.pdf

3

Thanks Vsr1.It is working.Great Help :slight_smile: