Reputation: 143
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
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