Couchbase query array

Question

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?

Prasad Varakur · Accepted Answer · 2017-01-05 10:17:38Z

2

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

answered Jan 5, 2017 at 10:17

Prasad Varakur

5292 silver badges3 bronze badges

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

1 Comment

geraldss Over a year ago

You can also use UNNEST.

Collectives™ on Stack Overflow

Couchbase query array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related