CODEWITHSUNDEEP
couchbase
Artem
Artem

Reputation: 2314

Select couchbase nested object

I have a bunch of documents with a structure like this:

{
  "month": 11,
  "year": 2017,
  //other fields
  "Cars":[
  {
    "CarId": 123,
    // other fields
  },
  {
    "CarId": 456,
    // other fields
  }
  // other cars
   ]
}

I am searching for a concrete car instance with id = 456. So far I have:

SELECT Cars
FROM DevBucket
WHERE year = 2017
    AND month = 11
    AND [CarId=456]

Couchbase returns correct document (which contains target car). However the output includes an array of all Car nodes within a document, but I'd like to have a single car (as if I use SELECT Cars[1] in the example above)

Search through couchbase tutorials didn't give me an answer. Is there a better way?

Upvotes: 2

Views: 1635

Answers (1)

Jeff Kurtz
Jeff Kurtz

Reputation: 661

Using the UNNEST clause you can perform "a join of the nested array with its parent object." This will produce an object for each nested element that includes the nested element as a top-level field, along with the rest of the original document (nested elements, and all).

This query will retrieve the car with an id of 456 when its parent object has a month and year of 11/2017.

SELECT car
FROM DevBucket db
UNNEST Cars car
WHERE car.CarId = 456 
    AND db.year = 2017
    AND db.month = 11;

Create this index for a quicker lookup than what you'll get with a Primary Index:

CREATE INDEX cars_index 
    ON DevBucket(DISTINCT ARRAY car.CarId FOR car IN Cars END);

For more information on UNNEST see NEST and UNNEST: Normalizing and Denormalizing JSON on the Fly

Upvotes: 2

Related Questions