How to query doc with child array and keep the parent-child structure

[air0ddc]

I am new to Couchbase and attempting to write my first complex query. I have a doc structure with parent information and a nested array of child information. I want the query to return portions of the parent and the children but maintain the structure of the children.
The example queries I found here retrieve only the child structure using unnest, however I need parent and child info.
Given the following example doc:

{
“docType”: “shipment”,
“origin”: “PointA”,
“destination”: “PointB”,
“orderTotal”: 10,
“contents”: [
{
“company”: “customer1”,
“orderCount”: 6,
“otherStuff”: “info we do not want returned”
},
{
“company”: “customer2”,
“orderCount”: 4,
“otherStuff”: “other info”
}
]
}

I want to return everything except “otherStuff” but maintain the array structure. Result:

{
“docType”: “shipment”,
“origin”: “PointA”,
“destination”: “PointB”,
“orderTotal”: 10,
“contents”: [
{
“company”: “customer1”,
“orderCount”: 6,
},
{
“company”: “customer2”,
“orderCount”: 4,
}
]
}

Unnest flattens the child sections,
Select * retains the structure however there is a lot more fields than just otherStuff that I want to exclude.

Thanks in advance for your assistance

[GauravJayaraj]

for the first part about unnest, you can use a subquery instead

SELECT d.destination, d.docType, d.orderTotal, d.origin , (SELECT c.company, c.orderCount FROM d.contents c) as contents  FROM `bucket` d;

Second part: Not sure of any other way other than explicitly listing all the projection fields you want to include

[dh]

To exclude a field from object elements in an array, you could:

SELECT b.*, ARRAY object_remove(c,"otherStuff") FOR c IN b.contents END contents
FROM `your-bucket` b

The constructed ARRAY aliased as the original field will replace it in the projection.

You could use the same technique to include only the fields you’re interested in - construct a new array of objects with just the fields of interest:

SELECT b.*, ARRAY {"company":c.company,"orderCount":c.orderCount} FOR c IN b.contents END contents
FROM `my-bucket` b

Obviously instead of “b.*” you could list the fields you want. If it is only a few you wish to exclude, then alias MISSING as them, e.g.

SELECT b.*, MISSING destination
FROM `my-bucket` b

to include all fields but “destination” in the results.

HTH.