I have
[
{
"some-app": {
"name": test,
"age": "34",
"company": [
{
"year": "2021",
"pa_flag": "N",
...
},
{
"year": "2022",
"pa_flag": "Y",
.....
},
.....
],
}
}
]
I want to select only name, age company.year and company.pa_flag
I am trying
Select name, age company.year and company.pa_flag FROM some-app LIMIT 10
but it only shows name and age columns
You can re-construct ARRAY the way you want using sub query expressions. https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/subqueries.html#subquery-n1ql-exp
SELECT a.name,
a.age,
(SELECT c.year, c.pa_flag
FORM a.company AS c
WHERE c.year > "2000"
ORDER BY c.year
OFFSET 0
LIMIT 10
) AS company
FROM `some-app` AS a
LIMIT 10;
Use [*] array syntax. For example:
select a.company[*].year
from soquestions._default.`some-app` a
(I put your document in a collection called some-app, using Couchbase 7, but the same thing should work if you're on an older version and just querying at the bucket level).
And that returns:
[
{
"year": [
"2021",
"2022"
]
}
]