Cloudant create views to count every last values array fields

Question

I have many json documents stored in a Cloudant database. The structure of document:

{
  "_id": "00229e31d5751d337abf409a4bd75492",
  "_rev": "1-d95d7ad32264d233453a0436b1557e7d",
  "timestamp": "2017-07-04T21:28:46.886Z",
  "APIresponse": {
    "intents": [
      {
        "intent": "greetings",
        "confidence": 1
      },
      {
        "intent": "machineBusy",
        "confidence": 0
      },
      {
        "intent": "set_weights",
        "confidence": 0
      },
      {
        "intent": "faqGuidelinesPAAmount",
        "confidence": 0
      },
      {
        "intent": "chat",
        "confidence": 0
      },
      {
        "intent": "feedback-no",
        "confidence": 0
      },
      {
        "intent": "faqGuidelinesWhatsnew",
        "confidence": 0
      },
      {
        "intent": "faqGuidelinesChildren",
        "confidence": 0
      },
      {
        "intent": "thanks",
        "confidence": 0
      },
      {
        "intent": "faqGuidelinesPAvsFederal",
        "confidence": 0
      }
    ],
    "entities": [],
    "input": {
      "text": "hey"
    },
    "output": {
      "text": [
        "Hey there!",
        "How a beautiful day to train :) Do you agree #firstName?"
      ],
      "nodes_visited": [
        "Greetings",
        "Initial greetings",
        "node_2_1495711348295"
      ],
      "error": "SpelEvaluationException when evaluating DialogNode Id [check PROFILING interrupted ASPFINDER]. Condition [$trainingContext.status == \"no_tp\" && $aspFinder.dob != null || $aspFinder.height != null || $aspFinder.weight != null || $aspFinder.impedIsPresent != null || $aspFinder.goal != null || $aspFinder.timesAWeek != null || $aspFinder.duration != null || $aspFinder.location != null || $aspFinder.level != null || $userContext.height != null] evaluated to FALSE.\norg.springframework.expression.spel.SpelEvaluationException: EL1007E: Property or field 'weight' cannot be found on null\n",
      "log_messages": [
        {
          "level": "err",
          "msg": "SpelEvaluationException when evaluating DialogNode Id [check PROFILING interrupted ASPFINDER]. Condition [$trainingContext.status == \"no_tp\" && $aspFinder.dob != null || $aspFinder.height != null || $aspFinder.weight != null || $aspFinder.impedIsPresent != null || $aspFinder.goal != null || $aspFinder.timesAWeek != null || $aspFinder.duration != null || $aspFinder.location != null || $aspFinder.level != null || $userContext.height != null] evaluated to FALSE.\norg.springframework.expression.spel.SpelEvaluationException: EL1007E: Property or field 'weight' cannot be found on null\n"
        }
      ]
    },
    "context": {
      "system": {
        "dialog_stack": [
          {
            "dialog_node": "node_2_1495711348295"
          }
        ],
        "dialog_request_counter": 5,
        "dialog_turn_counter": 5,
        "_node_output_map": {
          "node_3_1495711396477": [
            0
          ],
          "node_1_1495037449418": [
            0,
            0,
            1
          ],
          "node_12_1494424156763": [
            0,
            3,
            0,
            1,
            2,
            4
          ],
          "in exercise": [
            0
          ],
          "Workout Feedback": [
            0,
            2,
            1,
            0,
            3
          ],
          "Greetings": [
            0
          ],
          "node_2_1495711348295": [
            0,
            2,
            1,
            0
          ]
        }
      },
      "repeat": false,
      "lastMsg": [
        "Great job!",
        "We're done for today training! Great job :)"
      ],
      "conversation_id": "919d2d6b-e085-4fbe-bd12-19486aa0394e",
      "last": {
        "trainingContext": {
          "status": "no_workout",
          "suggested_workout_id": "W-fb2038d7d45a48a6af844a53f52a1759-T-74e57d317b9649feb5f024c049766fd7-F-technogym"
        },
        "notify_ui": "workout_closed",
        "output": {
          "nodes_visited": [
            "node_9_1494423774989",
            "node_1_1494427286758",
            "node_12_1494424156763"
          ],
          "text": [
            "Great job!",
            "We're done for today training! Great job :)"
          ],
          "log_messages": []
        }
      },
      "_currentSession": {
        "current_exe": 7
      },
      "currentSession": {
        "feedback": 5
      },
      "_timestamp": "2017-07-04T21:28:46.561Z",
      "trainingContext": {
        "status": "no_workout",
        "suggested_workout_id": "W-fb2038d7d45a48a6af844a53f52a1759-T-74e57d317b9649feb5f024c049766fd7-F-technogym"
      },
      "userContext": {
        "height": 160,
        "userID": "e37e39f4-61ba-41da-a356-2c6cbfae8392",
        "lastName": "#lastName",
        "facilityId": "4419597c-b63e-4358-b8ba-cc388bb192c3",
        "weight": 56,
        "userToken": "MjAxNzA3MDQyMTI4MzR8MzQyYzJkZmEyNGVkNGZlZDhiOWU5NDg0NWE1YzAxNTJ8ZWMxZDM4ZDdkMzU5NDhkMGE2MGNkOGMwYjhmYjlkZjl8MXxXLiBFdXJvcGUgU3RhbmRhcmQgVGltZXxpdC1JVHxlMzdlMzlmNDYxYmE0MWRhYTM1NjJjNmNiZmFlODM5Mnx8fHwxfDF8MHwxMDB8fHw1OHw2ODk0fDB8Y29tLm15d2VsbG5lc3M1.BC8BBB1E2066444DEA5F5E1F42627CF6443F3077",
        "firstName": "#firstName",
        "dob": "03/07/1986"
      }
    }
  }
}

The value odf the json I'm interested in is the always the last in the array field APIresponse.outout.nodes_visited. To be more specific, I would like to have a structure in which I obtain the count of every last value of the array field APIresponse.outout.nodes_visited and the associated value linked to that count (the name of count).

I usually use selector to query Cloudant but it seems that it does'nt provide a way to obtain the specific count I'm looking for.

I have tried to create a map reduce function in a design document but I have been not able to succeed.

Then the questions are:

How can I access to the last element of an array field in Cloudant?
How can I obtain the total count of every last element of the array in my database?

Thank you for any help,

Filippo

xpqz · Accepted Answer · 2017-07-07 15:39:53Z

1

You can do this with a map-reduce view. Firstly, key your emit with the field from the array with a value of 1:

function(doc) {
    if (doc && doc.APIresponse && 
        doc.APIresponse.output && doc.APIresponse.output.nodes_visited){
        var arr = doc.APIresponse.output.nodes_visited;
        emit(arr[arr.length-1], 1);
    }
}

and pick the built-in _count reduce function.

You should now be able to see all your nodes visited, with totals using something like

curl 'https://USER:[email protected]/DB/_design/DDOC/_view/VIEWNAME?group=true'

You can see your example data loaded in one of my databases:

https://skruger.cloudant.com/testdb -- it's open for reading.

I put the above suggested view function in, and here's the query:

curl 'https://skruger.cloudant.com/testdb/_design/ddoc/_view/nodes?group=true'
{"rows":[
    {"key":"ask exercise feedback","value":1},
    {"key":"Assign workout","value":2},
    {"key":"Capabilities","value":1},
    {"key":"Feedback_tester","value":1},
    {"key":"First Exercise","value":2},
    {"key":"mark exercise done","value":1},
    {"key":"no_tp","value":8},
    {"key":"node_1_1495716162431","value":10},
    {"key":"node_2_1495711348295","value":1},
    {"key":"node_21_1494245522737","value":1},
    {"key":"node_26_1494249967653","value":1},
    {"key":"node_3_1495716340965","value":2},
    {"key":"node_3_1495922533369","value":2},
    {"key":"node_4_1494233636522","value":2},
    {"key":"node_6_1493835340035","value":1},
    {"key":"node_7_1493835487968","value":1},
    {"key":"node_8_1492030091650","value":1},
    {"key":"node_8_1494235961152","value":2},
    {"key":"node_9_1495711958382","value":1},
    {"key":"pr exit service fail","value":7},
    {"key":"profiling - ending","value":8},
    {"key":"profiling - question duration","value":7},
    {"key":"profiling - question goal","value":7},
    {"key":"profiling - question impediments","value":7},
    {"key":"profiling - question level","value":7},
    {"key":"profiling - question location","value":7},
    {"key":"profiling - question timesWeek","value":7},
    {"key":"show next exercise","value":2}
]}

edited Jul 7, 2017 at 15:39

answered Jul 7, 2017 at 12:39

xpqz

3,73713 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Filippo Leoncini Over a year ago

Thank you very much for the answer. I follow your instructions but when I tun the curl commad I get this error: {"error":"service_unavaible": "reason" : "Service unavaible"}

xpqz Over a year ago

Can you make a subset of your data available for me to test? A couple of 100 docs, say.

Filippo Leoncini Over a year ago

Of course: dropbox.com/s/p7gav3vw6vsemx5/…

Filippo Leoncini Over a year ago

You are the best.

Collectives™ on Stack Overflow

Cloudant create views to count every last values array fields

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related