Search value on two nested arrays with OR statement

SQL++
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