querying part of array key in couchDB view

问题:

I currently have a document called beacon_logs, and it logs every time I walk into range of a beacon. The data looks similar to this:

{
  "_id": "00198cd8f0fc510dbad06bf24e93f55b",
  "_rev": "1-e90f025935847b0412923e4ba472cf2a",
  "device": "gwen",
  "beaconUUID": "123",
  "distance": "0.0",
  "timestamp": 1487443924
},
{
  "_id": "00198cd8f0fc510dbad06bf24e93f55c",
  "_rev": "1-e90f025935847b0412923e4ba472cf2a",
  "device": "gwen",
  "beaconUUID": "123",
  "distance": "0.1",
  "timestamp": 1487443925
},
{
  "_id": "01ab15fd3a1c7c37ba147be8c56fe389",
  "_rev": "1-587035fb7a71962c21f91b86aca56a77",
  "device": "gwen",
  "beaconUUID": "456",
  "distance": "0.87",
  "timestamp": 1487031602
},
{
  "_id": "01ab15fd3a1c7c37ba147be8c56fe388",
  "_rev": "1-587035fb7a71962c21f91b86aca56a77",
  "device": "gwen",
  "beaconUUID": "456",
  "distance": "0.87",
  "timestamp": 1487031603
}

And this view:

function (doc) {
  emit([doc.beaconUUID,doc.timestamp], doc);
}

What I want is to get only get all of a certain beaconuuid (i.e.123) and have it also sort by timestamp. This is the query I wrote:

*DB_NAME*/_design/*DDOC_NAME*/_view/*VIEW_NAME*?descending=false&startkey=["123",999999999]&endkey=["123",0]

however this returns me pretty random results that includes other beaconUUIDs as well.

My question to this very long winded explanation is: given that the key is an array, is there any way to query against 1 of the array's value, e.g.

*DB_NAME*/_design/*DDOC_NAME*/_view/*VIEW_NAME*?descending=false&key[0]="123"

and if not, would anyone be able to recommend a work around?

回答1:

using this view

function (doc) {
  if (doc.beaconUUID) {
    emit([doc.beaconUUID,doc.timestamp], doc);
  }
}

i was able to get the correct responses with these queries:

chronological timestamp order:
*DB_NAME*/_design/*DDOC_NAME*/_view/*VIEW_NAME*?startkey=["123",0]&endkey=["123",{}]

reverse chronological timestamp order:
*DB_NAME*/_design/*DDOC_NAME*/_view/*VIEW_NAME*?startkey=["123",{}]&endkey=["123",0]&descending=true

回答2:

Have you considered using Cloudant query?

Define an index

POST /$DATABASE/_index HTTP/1.1

{ 
 "index": {
   "fields": [
    "beaconUUID",
    "timestamp"
   ]
 },
 "type": "json"
}

... and query it

POST /$DATABASE/_find HTTP/1.1

passing the query in the BODY

{
 "selector": {
  "beaconUUID": {
   "$eq": "123"
  }
 },
 "sort": [
   "beaconUUID",
   "timestamp"
 ]
}

P.S. In case you are wondering why beaconUUID is included in the sort definition refer to Cloudant: How to create an index for "Sort" function?

回答3:

Just for anyone else looking for alternatives to this question, here are my findings regarding _find.

_find query

{
    'selector': {
        'beaconUUID':'123'
    },
    'fields': ['beaconUUID', 'timestamp'],
    'sort': [
        {'beaconUUID':'desc'},
        {'timestamp':'desc'}
    ]
}

Using _find

via PHP CURL

$url = 'https://'.<ACCOUNTNAME>.'.cloudant.com/<DB_NAME>/_find';
$fields = array(
                'selector' => array('beaconUUID'=>"123"),
                'fields' => array("beaconUUID",
                                  "timestamp"),
                'sort' => array(array("beaconUUID"=>"desc"), 
                                array("timestamp"=>"desc"))
            );
$fields_string = json_encode($fields);
$ch = curl_init();  

    curl_setopt($ch,CURLOPT_URL,$url);
    curl_setopt($ch, CURLOPT_USERPWD, <APIKEY> . ":" . <APIPASSWORD>);       
    curl_setopt($ch,CURLOPT_RETURNTRANSFER,true);
    curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");                                                                  
    curl_setopt($ch, CURLOPT_POSTFIELDS, $fields_string); 
    curl_setopt($ch, CURLOPT_HTTPHEADER, array(
        'Content-type: application/json',
        'Accept: */*'
    ));

    $output=curl_exec($ch);

    curl_close($ch);
    echo $output;

via PHP-On-Couch

require_once 'PHP-on-Couch/Couch.php';
require_once 'PHP-on-Couch/CouchAdmin.php';
require_once 'PHP-on-Couch/CouchClient.php';
require_once 'PHP-on-Couch/CouchDocument.php';
require_once 'PHP-on-Couch/CouchReplicator.php';
require_once 'PHP-on-Couch/Exceptions/CouchException.php';
require_once 'PHP-on-Couch/Exceptions/CouchConflictException.php';
require_once 'PHP-on-Couch/Exceptions/CouchExpectationException.php';
require_once 'PHP-on-Couch/Exceptions/CouchForbiddenException.php';
require_once 'PHP-on-Couch/Exceptions/CouchNoResponseException.php';
require_once 'PHP-on-Couch/Exceptions/CouchNotFoundException.php';
require_once 'PHP-on-Couch/Exceptions/CouchUnauthorizedException.php';


use PHPOnCouch\Couch, 
PHPOnCouch\CouchAdmin, 
PHPOnCouch\CouchClient;

$fullurl = 'http://'.<APIKEY>.':'.<APIPASSWORD>.'@'.<ACCOUNTNAME>.'.cloudant.com';
$client = new CouchClient($fullurl,$DATABASE);
$sort = [
            ["beaconUUID" => 'desc'],
            ["timestamp" => 'desc']
        ];
$fields = ['beaconUUID', 'timestamp'];
$find = ['beaconUUID'=>"123"];
$docs = $client->skip(10)->limit(30)->sort($sort)->fields($fields)->find($find);
echo json_encode($docs);