CODEWITHSUNDEEP
couchbase
dayuloli
dayuloli

Reputation: 17021

Couchbase - SELECT a subset of fields from array of objects

I am using the travel-sample data set, and am running the following query:

SELECT id, schedule FROM `travel-sample`WHERE type = "route" LIMIT 1;

It is returning with the following results:

[
  {
    "id": 10000,
    "schedule": [
      {
        "day": 0,
        "flight": "AF198",
        "utc": "10:13:00"
      },
      {
        "day": 0,
        "flight": "AF547",
        "utc": "19:14:00"
      },
      ...
    ]
  }
]

However, I don't want to return the schedule.$.day field; i.e. I want my results to be:

[
  {
    "id": 10000,
    "schedule": [
      {
        "flight": "AF198",
        "utc": "10:13:00"
      },
      {
        "flight": "AF547",
        "utc": "19:14:00"
      },
      ...
    ]
  }
]

How can I SELECT only a subset of object fields from an array of objects?

I have tried UNNEST but I don't want to have a separate record for each schedule element - I want the schedule elements to remain nested inside the document.

I have also tried using OBJECT_REMOVE

SELECT id, ARRAY OBJECT_REMOVE(x, 'day') FOR x in schedule END AS schedule FROM `travel-sample` WHERE type = "route" LIMIT 1;

But I want to whitelist rather than blacklist fields.

Upvotes: 0

Views: 1623

Answers (1)

d4nyll
d4nyll

Reputation: 12637

Your last attempt was close. Instead of using OBJECT_REMOVE, you can simply construct the object you want returned.

SELECT id, ARRAY {"flight": x.flight, "utc": x.utc} FOR x in schedule END AS schedule FROM `travel-sample` WHERE type = "route" LIMIT 1;

You will get the following results:

[
  {
    "id": 10000,
    "schedule": [
      {
        "flight": "AF198",
        "utc": "10:13:00"
      },
      {
        "flight": "AF547",
        "utc": "19:14:00"
      },
      ...
    ]
  }
]

Upvotes: 3

Related Questions