Nested arrays ANY IN SATISFIES query predicate

SQL++
1

Hello, the following are the steps to create my test case environment along with my example test doc that I am trying to query against, and then I present the specific query that I am having issues with.

To create the nested array document to test on, I have successfully run this update:

UPDATE `travel-sample`
SET v1.aaa=[{"bbb":"_21","id":"a1"},{"bbb":"_22","id":"b2"},{"bbb":"_23","id":"c3"}]
FOR v1 IN reviews WHEN v1.author="Nedra Cronin"  END
WHERE META().id="hotel_10063"
LIMIT 1 RETURNING reviews

Which returns this updated example doc that I want to run the query of interest on:

{
  "address": "6 rue aux Juifs",
  "alias": "Les Rouges Gorges",
  ...,
  "reviews": [
    ...,
    {
      "aaa": [
        {
          "bbb": "_21",
          "id": "a1"
        },
        {
          "bbb": "_22",
          "id": "b2"
        },
        {
          "bbb": "_23",
          "id": "c3"
        }
      ],
      "author": "Nedra Cronin",
      "content": "We ended up choosing the Holiday Inn because...",
      "date": "2012-01-14 02:15:51 +0300",
      "ratings": {
        "Overall": 4
      }
    },
    ...
  ],
  "state": "Haute-Normandie",
  "title": "Giverny",
  "tollfree": null,
  "type": "hotel",
  "url": "http://givernyguesthouse.com/robin.htm",
  "vacancy": true
}

Then I tried this query, it runs without error but returns no results:

SELECT * FROM `travel-sample` WHERE
ANY v2 IN v1, v1 IN reviews
SATISFIES v2.id="b2" AND v1.author="Nedra Cronin" END

I believe I have the correct syntax, but I assume I need to create an index since the ANY IN SATISFIES is located in the predicate, but these are the only references that I could find which do not provide examples of how to do this:
https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/collectionops.html#collection-op-any

Scroll down to “Query 3” after clicking the following link:

A related post on nested arrays, but for FOR IN WHEN updates:
https://www.couchbase.com/forums/t/update-set-for-in-when-end-chain

Thanks,
George

2

ANY v2 IN v1, v1 IN reviews

Syntax is loop simultaneously different arrays reviews , v1 in this case u don’t have v1 terminates .

Assume d is document  ANY v2 IN d.v1, v1 IN d.reviews
for (i =0, j= 0; i < len(d.v1) && j < len(d.reviews); i++, j++ ) {
           v2 = d.v1[i];
           v1 = reviews[j]
}

The syntax for nested arrays are as follows. loop inside loop

    SELECT *
     FROM `travel-sample` WHERE
    ANY  v1 IN reviews SATISFIES  v1.author="Nedra Cronin" AND  (ANY v2 IN v1.aaa SATISFIES v2.id="b2" END ) END;

Check out Example 4 https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

Also Index advisor service https://index-advisor.couchbase.com/indexadvisor/#1

3

Excellent, that worked, thanks!