How to get particular fields only from array

Question

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

vsr · Answer 1 · 2022-04-27T14:44:19.883

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;

score 0 · Accepted Answer · answered Apr 27 '22 at 13:33

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"
    ]
  }
]

How to get particular fields only from array

2 Answers2