Search value on two nested arrays with OR statement

SQL++
1

Hello everyone,

I’ve been struggling with a certain query for some time now and that’s why I decided I should discuss it here in order to find a solution. It’s a N1QL related issue.
I have this schema:

{
“attribute1” : “test”,
“attribute2” : “test”,
“array1” : [
{
“nestedAttr” : “SUF1”
},
{
“nestedAttr” : “SUF2”
},
],
“array2” : [
{
“nestedAttr” : “SUF1”
},
{
“nestedAttr” : “SUF2”
},
]
}

The query I am trying to run is this:

SELECT *
FROM default
WHERE attribute1 = “test”
AND attribute2 = “test”
AND
(
ANY arr1 IN array1 SATISFIES arr1.nestedAttr = ‘SUF1’ END
OR ANY arr2 IN array2 SATISFIES arr2.nestedAttr = ‘SUF1’ END
)

I have created two indexes for this query:
CREATE INDEX default_nested_index_1 ON default(attribute1,attribute2,(all (array (arr1.nestedAttr) for arr1 in array1 end)))
CREATE INDEX default_nested_index_2 ON default(attribute1,attribute2,(all (array (arr2.nestedAttr) for arr2 in array2 end)))

Now when I search only in the first array1 it works and the proper index is being used. But when I try the two arrays it doesnt use the two indexes and its taking a lot of time to execute.

Any ideas?

2

What’s your Couchbase version?
One way to get this is write the query as a UNION query.

(SELECT *
FROM default
WHERE attribute1 = “test”
AND attribute2 = “test”
AND
ANY arr1 IN array1 SATISFIES arr1.nestedAttr = ‘SUF1’ END
)
UNION
(SELECT *
FROM default
WHERE attribute1 = “test”
AND attribute2 = “test”
AND
ANY arr2 IN array2 SATISFIES arr2.nestedAttr = ‘SUF1’ END
)

_

In Couchbase 5.5, it does use a union scan over two array indexes.
I’ll have to see if this was addressed in earlier versions.

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "UnionScan",
        "scans": [
          {
            "#operator": "DistinctScan",
            "scan": {
              "#operator": "IndexScan3",
              "index": "default_nested_index_1",
              "index_id": "94df20d09a4f39d6",
              "index_projection": {
                "primary_key": true
              },
              "keyspace": "test",
              "namespace": "default",
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"test\"",
                      "inclusion": 3,
                      "low": "\"test\""
                    },
                    {
                      "high": "\"test\"",
                      "inclusion": 3,
                      "low": "\"test\""
                    },
                    {
                      "high": "\"SUF1\"",
                      "inclusion": 3,
                      "low": "\"SUF1\""
                    }
                  ]
                }
              ],
              "using": "gsi"
            }
          },
          {
            "#operator": "DistinctScan",
            "scan": {
              "#operator": "IndexScan3",
              "index": "default_nested_index_2",
              "index_id": "7a23c4466fe38d2c",
              "index_projection": {
                "primary_key": true
              },
              "keyspace": "test",
              "namespace": "default",
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"test\"",
                      "inclusion": 3,
                      "low": "\"test\""
                    },
                    {
                      "high": "\"test\"",
                      "inclusion": 3,
                      "low": "\"test\""
                    },
                    {
                      "high": "\"SUF1\"",
                      "inclusion": 3,
                      "low": "\"SUF1\""
                    }
                  ]
                }
              ],
              "using": "gsi"
            }
          }
        ]
      },
      {
        "#operator": "Fetch",
        "keyspace": "test",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "((((`test`.`attribute1`) = \"test\") and ((`test`.`attribute2`) = \"test\")) and (any `arr1` in (`test`.`array1`) satisfies ((`arr1`.`nestedAttr`) = \"SUF1\") end or any `arr2` in (`test`.`array2`) satisfies ((`arr2`.`nestedAttr`) = \"SUF1\") end))"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "self",
                  "star": true
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT *\nFROM test\nWHERE attribute1 = \"test\"\nAND attribute2 = \"test\"\nAND\n(\nANY arr1 IN array1 SATISFIES arr1.nestedAttr = \"SUF1\" END\nOR ANY arr2 IN array2 SATISFIES arr2.nestedAttr = \"SUF1\" END\n)"
}
1 Like
3

I use this version
Community Edition 5.0.0 build 3519