Search code examples
couchbasesql++

How to get particular fields only from array

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

Solution

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