CouchDB Mango Queries (CouchDB 2.0.1)

Question

I am trying to query the following data:

(obtained from:https://dotnetcodr.com/2017/06/21/introduction-to-couchdb-with-net-part-17-starting-with-mango-queries/)

{
      "post_code": 35801,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Huntsville",
          "longitude": -86.5673,
          "state": "Alabama",
          "state_abbreviation": "AL",
          "latitude": 34.7269
        }
      ]
    },

    ..........

My question:

How to include, say, firstly, the place_name, in the query.

The following javascript does not give a result:

{
    "selector": {
        "post_code": {"$eq": 35801}
    },
    "fields":["places.placename"]
}

correct index on post_code:

{
  "index": {
    "fields": [
      "post_code"
    ]
  },
  "type": "json",
  "name": "post-code-index"
}

I did set up an additional index as follows:

{
  "index": {
    "fields": [
      "places.placename"
    ]
  },
  "type": "json"
}

What if the data had the following field:

"old_post_code_numbers": [12345, 67890, ......]

or:

What if the data had the following fields:

"places": 
{
  "place_name": "Huntsville",
  "longitude": -86.5673,
  "state": "Alabama",
  "state_abbreviation": "AL",
  "latitude": 34.7269
}

JSON has so many forms and I struggle to get a handle on the principles behind the javascript required for these kind of queries. I know it is simple. It must be simple.

Many thanks for any guidance or websites I should/could visit.

Edit:

I managed to get the following to work on an online javascript testing site, but I still could not get this to work in CouchDB Mango. Surely this is possible in CouchDB.???

var xxx=
     {
      "post_code": 82941,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Pinedale",
          "longitude": -109.8561,
          "state": "Wyoming",
          "state_abbreviation": "WY",
          "latitude": 42.8543
        }]
 }

console.log(xxx.places[0].place_name);

The tricky thing with your request is that you are trying to return an array element properties. Therefore, I don't think it's supported by Cloudant/CouchDB. For this purpose, you could use map/reduce. — Alexis Côté, Aug 31 '17 at 12:08
Hi @Alexis. Thank you for your comment re map reduce. I have added an edit (above) but maybe it is irrelevant. — jlb333333, Aug 31 '17 at 15:15
@Alexis Yes, map/reduce worked with `places[0].place_name`. Thanks. — jlb333333, Aug 31 '17 at 15:41
Hi @Alexis. Could you have a look at my proposed answer if you have a moment please. — jlb333333, Sep 04 '17 at 09:17

jlb333333 · Answer 1 · 2017-09-04T12:16:21.510

I have been able to get a result for part 1. of my question (based on the answer to this question), as follows:

My index:

{
"type": "json",
 "def": {
  "fields": [
   {
    "places.0.place_name": "asc"
   }
  ]
 }
}

My query:

{
  "selector": {
    "places.0.place_name": {
        "$gte": null
      }

  },
  "fields": [
    "_id",
    "places.0.place_name"
  ],
  "sort": [
    {
      "places.0.place_name": "asc"
    }
  ]
}

Result:

{"docs":[
{"_id":"254b9a8c7a46934363076cc3d9034082","places":{"0":{"place_name":"Aberdeen"}}},
{"_id":"254b9a8c7a46934363076cc3d9037559","places":{"0":{"place_name":"Altavista"}}},
{"_id":"254b9a8c7a46934363076cc3d900e4d2","places":{"0":{"place_name":"Anchorage"}}},
{"_id":"254b9a8c7a46934363076cc3d900f3b9","places":{"0":{"place_name":"Anchorage"}}},
{"_id":"254b9a8c7a46934363076cc3d902c738","places":{"0":{"place_name":"Ashland"}}},
{"_id":"254b9a8c7a46934363076cc3d901a2f2","places":{"0":{"place_name":"Atlanta"}}},
{"_id":"254b9a8c7a46934363076cc3d901a374","places":{"0":{"place_name":"Atlanta"}}} 
.................

In this case I have omitted the "post_code": {"$eq": 35801} but this can be added without an error occurring.

part3:

Index:

{
  "index": {
    "fields": [
      {
        "places.place_name": "asc"
      }
    ]
  },
  "type": "json"
}

Query:

{
  "selector": {
    "places.place_name": {
        "$gte": null
      }

  },
  "fields": [
    "places.place_name"
  ],
  "sort": [
    {
      "places.place_name": "asc"
    }
  ]
}

Result:

{"docs":[
{"places":{"place_name":"Anchorage"}},
{"places":{"place_name":"Anchorage"}},
{"places":{"place_name":"Huntsville"}},
{"places":{"place_name":"Huntsville"}},
{"places":{"place_name":"Phoenix"}},
{"places":{"place_name":"Phoenix"}}
]}

Part 2:

Index:

{
  "index": {
    "fields": [
      {
        "old_post_code_numbers": "asc"
      }
    ]
  },
  "type": "json"
}

Query:

{
  "selector": {
    "old_post_code_numbers": {
        "$gte": null
      }

  },
  "fields": [
    "places.place_name",
    "old_post_code_numbers"
  ],
  "sort": [
    {
      "old_post_code_numbers": "asc"
    }
  ]
}

Result:

{"docs":[
{"places":{"place_name":"Huntsville"},"old_post_code_numbers":[12345,67890]},
{"places":{"place_name":"Huntsville"},"old_post_code_numbers":[12345,67890]},
{"places":{"place_name":"Anchorage"},"old_post_code_numbers":[12345,67890]},
{"places":{"place_name":"Anchorage"},"old_post_code_numbers":[12345,67890]},
{"places":{"place_name":"Phoenix"},"old_post_code_numbers":[12345,67890]},
{"places":{"place_name":"Phoenix"},"old_post_code_numbers":[12345,67890]}
]}

The last example using: "old_post_code_numbers.0" throughout, gives:

{"docs":[
{"places":{"place_name":"Huntsville"},"old_post_code_numbers":{"0":12345}},
{"places":{"place_name":"Huntsville"},"old_post_code_numbers":{"0":12345}},
{"places":{"place_name":"Anchorage"},"old_post_code_numbers":{"0":12345}},
{"places":{"place_name":"Anchorage"},"old_post_code_numbers":{"0":12345}},
{"places":{"place_name":"Phoenix"},"old_post_code_numbers":{"0":12345}},
{"places":{"place_name":"Phoenix"},"old_post_code_numbers":{"0":12345}}
]}

I would appreciate any comments.

CouchDB Mango Queries (CouchDB 2.0.1)

1 Answers1