CODEWITHSUNDEEP
jsoncouchbasesql++
Philipp Hellmayr
Philipp Hellmayr

Reputation: 302

Couchbase query array

I have the following json saved in my couchbase bucket "geo".

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {
        "ID": "1753242",
        "TYPE": "8003"
      }
    },
    {
      "type": "Feature",
      "properties": {
        "ID": "4823034",
        "TYPE": "7005"
      }
    },
    {
      "type": "Feature",
      "properties": {
        "ID": "4823034",
        "TYPE": "8003"
      }
    }
  ]
}

To get all "features" with "properties.TYPE : 8003" I tried the following.

SELECT features FROM geo
WHERE ANY f IN features SATISFIES f.properties.TYPE = "8003" END;

But this returns the whole json document and not just the "features" with the properties.TYPE "8003". Does anybody know, how to query to get just the matching features with "properties.TYPE": "8003" as result?

Upvotes: 0

Views: 117

Answers (1)

Prasad Varakur
Prasad Varakur

Reputation: 529

The ANY expression in the WHERE-clause is used to only filter the documents of interest. If you want specific project list, you need to write corresponding expression in the projection list as well. The projection in your query is asking for 'features' and hence the whole 'features' array is being returned. You can write following expression on the projection list to get the output you want:

SELECT ARRAY f FOR f IN features WHEN f.properties.TYPE = "8003" END
FROM geo
WHERE ANY f IN features SATISFIES f.properties.TYPE = "8003" END;
[
  {
    "$1": [
      {
        "properties": {
          "ID": "1753242",
          "TYPE": "8003"
        },
        "type": "Feature"
      },
      {
        "properties": {
          "ID": "4823034",
          "TYPE": "8003"
        },
        "type": "Feature"
      }
    ]
  }
]

hth,

-Prasad

Upvotes: 2

Related Questions