How to create equivalent view in cloudant for distinct and cout query in mysql

Question

I have 1000 documents of type below and I want to design a document in cloudant (write a view) equivalent to this Mysql query:

 (SELECT 
      COUNT(DISTINCT(correlationassetid)), PERIOD 
  FROM 
      view_asset 
  WHERE 
      ((PERIOD >= '201705') AND (PERIOD <= '201705')) 
  GROUP BY 
      PERIOD 
  ORDER BY 
      PERIOD ASC)

I have tried below view but it does not give proper result. could any one help me?

function (doc) {
  if(doc.type == "asset"){
    emit(doc.period,1);
  }

{
  "_id": "24dee0ec910e22605fe8fc4189000c56",
  "_rev": "1-d667f0b4ce3d984c0d7aafadd223674a",
  "parentName": "",
  "period": "201701",
  "providerGlobalAssetId": "",
  "cost": "5",
  "providerRegionCode": "",
  "owner": "",
  "snapshotId": "659c5f7a-35d62", 
  "correlationAssetId": "aws-6082634880291"
}

Possible duplicate of [count number of rows in cloudant in response](https://stackoverflow.com/questions/44106569/count-number-of-rows-in-cloudant-in-response) — Raj, May 24 '17 at 16:58

ptitzler · Answer 1 · 2017-05-20T21:27:40.120

I'm a bit confused by your example query and your sample document because there are some inconsistencies, but I am going to try to answer your question based on a couple assumptions.

If you are filtering by a single PERIOD value no GROUPBY clause should be required in your SQL statement:

SELECT COUNT(DISTINCT(correlationassetid))
FROM view_asset 
WHERE PERIOD = '201705'

Defining the following design document

{
  "_id": "_design/howdoi",
  "views": {
    "filter-by": {
      "map": "function (doc) {\n  if(doc.type === \"asset\") {\n   emit([doc.period, doc.correlationAssetId], 1);\n  }\n}",
      "reduce": "_count"
    }
  },
 "language": "javascript"
}

a GET request to query the view

 https://$USER:$PASSWORD@$HOST/$DATABASE/_design/howdoi/_view/filter-by?inclusive_end=true&start_key=[%22201705%22]&end_key=[%22201705%22%2C{}]&reduce=true&group_level=2

should return the desired result if you specify start_key=["201705"] and end_key=["201705",{}].

Example response if there are two documents within that period, both containing the same correlationAssetId:

 {
  "rows": [
   {
    "key": [
      "201705",
      "aws-6082634880291"
    ],
    "value": 2
  }
 ]
}

The number of rows in the result set should identify the distinct number of correlationAssetIds for the specified PERIOD.

Example result for two correlationAssetIds:

{
  "rows": [
    {
      "key": [
        "201701",
        "aws-6082634880291"
      ],
      "value": 1
    },
    {
     "key": [
       "201701",
       "aws-6082634880292"
     ],
     "value": 1
   }
  ]
 }

P.S. Your example document didn't define the asset property and your view definition would therefore not have returned the document. My response above assumes that that property is defined in the documents.

Thanks ptitzler. Yes i missed the type: asset in document(it was a long document and while making it short to paste here by mistake i removed type property.) — Lucifer007, May 21 '17 at 08:15
With your above solution, yes i am getting correct number of rows,(please bear with me i can ask some silly quetions as i am new to cloudant) is there any way to count and show the total number of rows in response. For example i am getting total rows as 1810 which is correct, but i want to see the response in somewhat below format... {"rows":[ {"key":["201705"],"value":1810} ]} — Lucifer007, May 21 '17 at 09:13
Now how can i count the number of rows and show in response something like {"rows":[ {"key":["201705"],"value":1810} ]} — Lucifer007, May 22 '17 at 06:02

How to create equivalent view in cloudant for distinct and cout query in mysql

1 Answers1

Linked