N1QL search certain objects within the object-CodePudding

I have multiple documents in my couchbase bucket with the following structure:

{
  "objectType": "someObjectType",
  "id": "1",
  "latest": {
    "id": "1",
    "version": "biyr4jqi4nny"
  },
  "history": {
    "bi11b295bjng": {
      "id": "1",
      "version": "bi11b295bjng"
    }
    "bi1189wx1h6v": {
      "id": "1",
      "version": "bi1189wx1h6v"
    }
  }
}

As seen in the snippet above, history is an object of objects. What I'm trying to do is selecting objectType, id, latest and history, but history should include only the object specified in query instead of all the history (which may be vast).

My query looks like this:

SELECT
    bucket.id, 
    bucket.objectType, 
    bucket.latest, 
    bucket.history.bi11b295bjng
FROM bucket WHERE objectType = 'someObjectType'

Which produces me the following response:

[
  {
    "objectType": "someObjectType",
    "id": 1,
    "latest": {
      "id": "9mobile_NG_001-ROW",
      "version": "biyr4jqi4nny"
    },
    "biyr4jqi4nny": {
      "id": "1",
      "version": "biyr4jqi4nny"
    }
  }
]

Queried object got unwrapped from the parent one. Desired output should look like this:

[
  {
    "objectType": "someObjectType",
    "id": 1,
    "latest": {
      "id": "9mobile_NG_001-ROW",
      "version": "biyr4jqi4nny"
    },
    "history": {
      "biyr4jqi4nny": {
        "id": "1",
        "version": "biyr4jqi4nny"
      }
    }
  }
]

How could I get history.{version} without losing the parent object?

CodePudding user response：

Construct object and Alias as history

SELECT
    b.id, 
    b.objectType, 
    b.latest, 
    { b.history.bi11b295bjng } AS history
FROM bucket AS b 
WHERE b.objectType = "someObjectType";

If need multiple objects of same field

SELECT
    b.id, 
    b.objectType, 
    b.latest, 
    { b.history.bi11b295bjng, b.history.bi1189wx1h6v } AS history
FROM bucket AS b 
WHERE b.objectType = "someObjectType";

If you have many want to remove one of them

SELECT
    b.id, 
    b.objectType, 
    b.latest, 
    OBJECT_REMOVE(b.history,"bi11b295bjng") AS history
FROM bucket AS b 
WHERE b.objectType = "someObjectType";