CODEWITHSUNDEEP
jsoncouchbasesql++
Emraan
Emraan

Reputation: 143

Group json data based on one attribute on querying couchbase

I can retrive the below data in couchbase using query.

select distinct SupplierName, Currency, CountryOfOrigin from RangePlan where type = "Supplier"

[{ "CountryOfOrigin": "China", "currency": "USD", "supplierName": "abc" },

{ "CountryOfOrigin": "China", "currency": "USD", "supplierName": "bcd" },

{ "CountryOfOrigin": "India", "currency": "USD", "supplierName": "hij" },

{ "CountryOfOrigin": "India", "currency": "USD", "supplierName": "klm" }]

Now I need help in getting the data based on the Country of Origin as below.

{ 
 "china" : [ 
  { 
    "supplierName" : "abc", 
    "currency" : "USD" 
  } ,
  { 
    "supplierName" : "bcd", 
    "currency" : "USD" 
  } 
] 
 } 
  "india" : [ 
{ 
    "supplierName" : "hij", 
    "currency" : "INR" 
  } ,
  { 
    "supplierName" : "klm", 
    "currency" : "INR" 
  }     
  ] 
}

I need a query to the above output.

Thanks, Emraan

Upvotes: 0

Views: 27

Answers (1)

vsr
vsr

Reputation: 7414

SELECT RAW OBJECT v.CountryOfOrigin: v.val FOR v IN (SELECT q.CountryOfOrigin , ARRAY_AGG({"currency":q.Currency,"supplierName":q.SupplierName}) val FROM (SELECT DISTINCT SupplierName, Currency, CountryOfOrigin from RangePlan WHERE type = "Supplier") As q GROUP BY q.CountryOfOrigin) END;

Upvotes: 1

Related Questions