Couchbase query filtering by tag in an array

user_9643459 0
Viewed 180

I have a author bucket. And in this bucket I keep the author infos and author's articles. I want to select the articles that have the tags I want from the author bucket.

I have tried this but I could not find how to do the filtering.

SELECT art.* FROM author AS a 
UNNEST a.articles AS art
WHERE art.tags = 'History'

This is author bucket:

{
  "about": {
    "name": "sassa",
    "userName": "sassatur"
  },
  "articles": [
    {
      "authorId": [
        "8c7ba33e-0674-4d99-bfad-29d144028bc9"
      ],
      "claps": [],
      "comments": [],
      "content": {
        "articleType": "HTML",
        "data": "My First Article"
      },
      "id": "71d6fa22-61be-4a93-8e86-8d569080da97",
      "publishStatus": "UNLISTED",
      "statistic": {
        "articleId": "71d6fa22-61be-4a93-8e86-8d569080da97",
        "views": [
          1602683127039,
          1602683148270
        ]
      },
      "tags": [
        "Art, History"
      ],
      "title": "Culture"
    },
    {
      "authorId": [
        "8c7ba33e-0674-4d99-bfad-29d144028bc9"
      ],
      "claps": [],
      "comments": [],
      "content": {
        "articleType": "HTML",
        "data": "My First Article"
      },
      "id": "81d6fa22-63be-4a93-8e86-8d569080da97",
      "publishStatus": "UNLISTED",
      "statistic": {
        "views": [
          1602683127039,
          1602683148270
        ]
      },
      "tags": [
        "Art"
      ],
      "title": "Culture"
    }
  ],
  "id": "8c7ba33e-0674-4d99-bfad-29d144028bc9",
}
n1ql couchbase sql
1 Answers

Try using ANY/IN/SATISFIES, like so:

SELECT art.* FROM author AS a 
UNNEST a.articles AS art
WHERE ANY x IN art.tags SATISFIES x == 'Art' END;

This works for 'Art' in your example, but not 'History' because of the way you are storing tags. It's an array, but it appears to have a single(?) item with comma-separated values. So, instead of "tags": ["Art,History"], I would recommend: "tags": ["Art","History"] instead, and then it will work.

However, if you are stuck with the comma-separate string, you can use SPLIT and ARRAY_CONTAINS as well:

SELECT art.* FROM author AS a 
UNNEST a.articles AS art
WHERE ANY x IN art.tags SATISFIES ARRAY_CONTAINS(SPLIT(x,", "), 'History') END;
user_400150
Related