How to group the result to bring an array structure

n1ql
query

#1

How can i group and bring a portion of the result into an array structure in N1QL ?
I want the outlets as an array within each brand.

Document Id - brand_1
{
	"brandId": 1,
	"category": "Shopping",
	"images": [
	  {
		"imageId": 11,
		"target": "MOBILE"
	  }
	]
}

Document Id - brand_2
{
	"brandId": 2,
	"category": "Movies",
	"images": [
	  {
		"imageId": 12,
		"target": "WEBSITE"
	  }
	]
}

Document Id - outlet_1
	{
	  "airport": "HKA",
	  "type": "Outlet",
	  "outletName": "Sample outlet1",
	  "brandId": 1
	}
	
Document Id - outlet_2
	{
	  "airport": "CHG",
	  "type": "Outlet",
	  "outletName": "Sample outlet2",
	  "brandId": 1
	}

Document Id - outlet_3
	{
	  "airport": "TKY",
	  "type": "Outlet",
	  "outletName": "Sample outlet3",
	  "brandId": 2
	}	

N1QL:
SELECT brand.brandId, brand.category, brand.images, outlet
FROM sample_bucket_1 AS outlet
JOIN sample_bucket_1 AS brand
ON keys 'brand_'||to_STRING(outlet.brand.brandId) 
WHERE outlet.type = 'Outlet';

[
  {
    "brandId": 1,
    "category": "Shopping",
    "images": [
      {
        "imageId": 12,
        "target": "MOBILE"
      }
    ],
	"outlet": {
	  "airport": "HKA",
	  "type": "Outlet",
	  "outletName": "Sample outlet1",
	  "brandId": 1
	}
  },
  {
    "brandId": 1,
    "category": "Shopping",
    "images": [
      {
        "imageId": 12,
        "target": "MOBILE"
      }
    ],
	"outlet": {
	  "airport": "CHG",
	  "type": "Outlet",
	  "outletName": "Sample outlet2",
	  "brandId": 1
	}
  },
  {
    "brandId": 2,
    "category": "Movies",
    "images": [
      {
        "imageId": 13,
        "target": "WEBSITE"
      }
    ],
	"outlet": {
	  "airport": "CHG",
	  "type": "Outlet",
	  "outletName": "Sample outlet2",
	  "brandId": 2
	}
  }
]

Expected output:

    [
      {
        "brandId": 1,
        "category": "Shopping",
        "images": [
          {
            "imageId": 12,
            "target": "MOBILE"
          }
        ],
    	"outlet": [
    		{
    		  "airport": "HKA",
    		  "type": "Outlet",
    		  "outletName": "Sample outlet1",
    		  "brandId": 1
    		},
    		{
    		  "airport": "CHG",
    		  "type": "Outlet",
    		  "outletName": "Sample outlet2",
    		  "brandId": 1
    		}
    	]
      },
      {
        "brandId": 2,
        "category": "Movies",
        "images": [
          {
            "imageId": 13,
            "target": "WEBSITE"
          }
        ],
    	"outlet": [
    		{
    		  "airport": "CHG",
    		  "type": "Outlet",
    		  "outletName": "Sample outlet3",
    		  "brandId": 2
    		}
    	]
      }
    ]

#2
[quote="g.radhakrishnan, post:1, topic:9705"]
SELECT brand.brandId, brand.category, brand.images, outlet
FROM sample_bucket_1 AS outlet
JOIN sample_bucket_1 AS brand
ON keys 'brand_'||to_STRING(outlet.brand.brandId) 
WHERE outlet.type = 'Outlet';
[/quote]

Use the ARRARY_AGG() to create an array of outlet for each brand.

SELECT brand.brandId, brand.category, brand.images, ARRAY_AGG(outlet)
FROM sample_bucket_1 AS outlet
JOIN sample_bucket_1 AS brand
ON keys 'brand_'||to_STRING(outlet.brand.brandId) 
WHERE outlet.type = 'Outlet'
GROUP BY brand.brandId, brand.category, brand.images;

#3

This is exactly what i wanted. Thanks very much Keshav…