Couchbase N1QL query to select value from object array

Question

What's the correct way to select a value from an array of objects?

Would like to retrieve a distinct list of the comments.

[
  "comment 1",
  "comment 2",
  "comment 3",
  "comment 4"
]

Current N1QL query

SELECT links[*].comment FROM `my-db`
WHERE type = "links" AND ANY l IN links SATISFIES l.comment IS NOT MISSING END

Results

[
  {
    "comment": [
      "comment 1"
    ]
  },
  {
    "comment": [
      "comment 1",
      "comment 2"
    ]
  },
  {
    "comment": [
      "comment 3",
      "comment 4"
    ]
  }
]

Given a series of documents that contain a comment field:

{
  "type": "links",
  "links": [
    {
      "comment": "comment 1"
    }
  ]
}

{
  "type": "links",
  "links": [
    {
      "comment": "comment 1"
    },
    {
      "comment": "comment 2"
    }
  ]
}

{
  "type": "links",
  "links": [
    {
      "comment": "comment 3"
    },
    {
      "comment": "comment 4"
    }
  ]
}

vsr · Accepted Answer · 2021-01-12 13:49:00Z

5

Use UNNEST

SELECT DISTINCT RAW l.comment 
FROM `my-db` AS m
UNNEST m.links AS l
WHERE m.type = "links" AND l.comment IS NOT NULL;

OR

If data set is not too large.

SELECT RAW  ARRAY_DISTINCT(ARRAY_FLATTEN(ARRAY_AGG(m.links[*].comment) ,2))
FROM `my-db` AS m
WHERE m.type = "links";

edited Jan 12, 2021 at 13:49

answered Jan 8, 2021 at 7:36

vsr

7,4441 gold badge13 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joshua Over a year ago

Both methods above return the expected results. The second did have a double nesting of array (eg. [ [ 'comment 1', 'comment 2' ] ] ) but that's easy to resolve in parsing. With the recommended index applied, both have response times of ~15ms.

vsr Over a year ago

change ARRAY_FLATTEN() second argument to 2 and try

Collectives™ on Stack Overflow

Couchbase N1QL query to select value from object array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related