Additional help with N1QL dynamic attribute key

SQL++
1

We have a situation very similar to this question Help N1QL dynamic attribute key

We have a json doc that looks like this:

{
  "type":"testType",
  "indexMap":{"key1":"val1", "key2":"val2"},
  "data":{"foo":"bar"}
}

We want to match docs where the key value pairs match our expected set, but we do not know how many pairs will be present or how many we will be trying to match.

We have an index:

CREATE INDEX `rec1-1_record_by_index_map` ON `record`((distinct (array `i` for `i` in object_pairs(`indexMap`) end)))

And make a statement:

SELECT r AS doc, meta(r).cas AS revision FROM record AS r WHERE any i in object_pairs(indexMap)satisfies i = { "name":"key1", "value":"val1"} end LIMIT 100

However when we have 2 entries to match we dynamically created a statement like this:

SELECT r AS doc, meta(r).cas AS revision FROM record AS r WHERE any i in object_pairs(indexMap)satisfies i = { "name":"key1", "value":"val1"} end AND any i in object_pairs(indexMap)satisfies i = { "name":"key2", "value":"val2"} end LIMIT 100

We do not match any docs with the above statement, but if we change to:

SELECT r AS doc, meta(r).cas AS revision FROM record AS r WHERE any i in object_pairs(indexMap)satisfies i = { "name":"key1", "value":"val1"} end AND any j in object_pairs(indexMap)satisfies j = { "name":"key2", "value":"val2"} end LIMIT 100

It works, at least from the couchbase console. Note the variable is renamed “j” in the final AND condition. So it seems like the query engine does not yield the expected result where we reuse the variable name maybe? But we do not know how many pairs will be trying to match, so we cannot just add “j” to the index, since we are in theory matching n pairs.

Is there a way to structure our index to accomplish this?

2

Using the same index, how about this. Edit: Sorry, the below gives you an OR, not an AND…

SELECT r AS doc, meta(r).cas AS revision
FROM record AS r
WHERE ANY i IN OBJECT_PAIRS( indexMap ) SATISIFIES i IN [ { "name":"key1", "value":"val1" },  { "name":"key2", "value":"val2" } ] END
LIMIT 100;
1 Like
3

Hi @m0thra,

I filed a bug because your original query should work: Loading...

Here is a workaround for 4.5.1. This will give you the equivalent of AND, and will use your index. Confirm index usage by using EXPLAIN, and check that both arms of the INTERSECT use the index.

SELECT r AS doc, meta(r).cas AS revision
FROM record AS r
WHERE ANY i IN OBJECT_PAIRS( indexMap ) SATISFIES i = { "name":"key1", "value":"val1" } END
INTERSECT
SELECT r AS doc, meta(r).cas AS revision
FROM record AS r
WHERE ANY i IN OBJECT_PAIRS( indexMap ) SATISFIES i = { "name":"key2", "value":"val2" } END
LIMIT 100;
4

Thanks for looking into this. One thing I was thinking is on the performance. Will the INTERSECT version always hit the index once for each SELECT? Asking because I think thia version:

SELECT r AS doc, meta(r).cas AS revision FROM record AS r WHERE any i in object_pairs(indexMap)satisfies i = { "name":"key1", "value":"val1"} end AND any j in object_pairs(indexMap)satisfies j = { "name":"key2", "value":"val2"} end LIMIT 100

could be better, if it just hits the index once grabs a bunch of docs, then does downstream filtering on the other AND. We have implemented, and using this one it is functional.

5

Please post the EXPLAIN for both.

6

Sure, “j” version:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "DistinctScan",
              "scan": {
                "#operator": "IndexScan",
                "index": "rec1-1_record_by_index_map",
                "index_id": "f5f767dbbf23f38d",
                "keyspace": "record",
                "namespace": "default",
                "spans": [
                  {
                    "Range": {
                      "High": [
                        "{\"name\":\"key1\",\"value\":\"val1\"}"
                      ],
                      "Inclusion": 3,
                      "Low": [
                        "{\"name\":\"key1\",\"value\":\"val1\"}"
                      ]
                    }
                  }
                ],
                "using": "gsi"
              }
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Fetch",
                    "as": "r",
                    "keyspace": "record",
                    "namespace": "default"
                  },
                  {
                    "#operator": "Filter",
                    "condition": "(any `i` in object_pairs((`r`.`indexMap`)) satisfies (`i` = {\"name\": \"key1\", \"value\": \"val1\"}) end and any `j` in object_pairs((`r`.`indexMap`)) satisfies (`j` = {\"name\": \"key2\", \"value\": \"val2\"}) end)"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "as": "doc",
                        "expr": "`r`"
                      },
                      {
                        "as": "revision",
                        "expr": "(meta(`r`).`cas`)"
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject"
                  }
                ]
              }
            }
          ]
        },
        {
          "#operator": "Limit",
          "expr": "100"
        }
      ]
    },
    "text": "SELECT r AS doc, meta(r).cas AS revision FROM record AS r WHERE any i in object_pairs(indexMap)satisfies i = { \"name\":\"key1\", \"value\":\"val1\"} end AND any j in object_pairs(indexMap)satisfies j = { \"name\":\"key2\", \"value\":\"val2\"} end LIMIT 100"
  }
]

INTERSECT version:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "IntersectAll",
          "first": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "DistinctScan",
                "scan": {
                  "#operator": "IndexScan",
                  "index": "rec1-1_record_by_index_map",
                  "index_id": "f5f767dbbf23f38d",
                  "keyspace": "record",
                  "namespace": "default",
                  "spans": [
                    {
                      "Range": {
                        "High": [
                          "{\"name\":\"key1\",\"value\":\"val1\"}"
                        ],
                        "Inclusion": 3,
                        "Low": [
                          "{\"name\":\"key1\",\"value\":\"val1\"}"
                        ]
                      }
                    }
                  ],
                  "using": "gsi"
                }
              },
              {
                "#operator": "Parallel",
                "~child": {
                  "#operator": "Sequence",
                  "~children": [
                    {
                      "#operator": "Fetch",
                      "as": "r",
                      "keyspace": "record",
                      "namespace": "default"
                    },
                    {
                      "#operator": "Filter",
                      "condition": "any `i` in object_pairs((`r`.`indexMap`)) satisfies (`i` = {\"name\": \"key1\", \"value\": \"val1\"}) end"
                    },
                    {
                      "#operator": "InitialProject",
                      "result_terms": [
                        {
                          "as": "doc",
                          "expr": "`r`"
                        },
                        {
                          "as": "revision",
                          "expr": "(meta(`r`).`cas`)"
                        }
                      ]
                    },
                    {
                      "#operator": "Distinct"
                    },
                    {
                      "#operator": "FinalProject"
                    }
                  ]
                }
              },
              {
                "#operator": "Distinct"
              }
            ]
          },
          "second": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "DistinctScan",
                "scan": {
                  "#operator": "IndexScan",
                  "index": "rec1-1_record_by_index_map",
                  "index_id": "f5f767dbbf23f38d",
                  "keyspace": "record",
                  "namespace": "default",
                  "spans": [
                    {
                      "Range": {
                        "High": [
                          "{\"name\":\"key2\",\"value\":\"val2\"}"
                        ],
                        "Inclusion": 3,
                        "Low": [
                          "{\"name\":\"key2\",\"value\":\"val2\"}"
                        ]
                      }
                    }
                  ],
                  "using": "gsi"
                }
              },
              {
                "#operator": "Parallel",
                "~child": {
                  "#operator": "Sequence",
                  "~children": [
                    {
                      "#operator": "Fetch",
                      "as": "r",
                      "keyspace": "record",
                      "namespace": "default"
                    },
                    {
                      "#operator": "Filter",
                      "condition": "any `i` in object_pairs((`r`.`indexMap`)) satisfies (`i` = {\"name\": \"key2\", \"value\": \"val2\"}) end"
                    },
                    {
                      "#operator": "InitialProject",
                      "result_terms": [
                        {
                          "as": "doc",
                          "expr": "`r`"
                        },
                        {
                          "as": "revision",
                          "expr": "(meta(`r`).`cas`)"
                        }
                      ]
                    },
                    {
                      "#operator": "Distinct"
                    },
                    {
                      "#operator": "FinalProject"
                    }
                  ]
                }
              },
              {
                "#operator": "Distinct"
              }
            ]
          }
        },
        {
          "#operator": "Limit",
          "expr": "100"
        }
      ]
    },
    "text": "SELECT r AS doc, meta(r).cas AS revision\r\nFROM record AS r\r\nWHERE ANY i IN OBJECT_PAIRS( indexMap ) SATISFIES i = { \"name\":\"key1\", \"value\":\"val1\" } END\r\nINTERSECT\r\nSELECT r AS doc, meta(r).cas AS revision\r\nFROM record AS r\r\nWHERE ANY i IN OBJECT_PAIRS( indexMap ) SATISFIES i = { \"name\":\"key2\", \"value\":\"val2\" } END\r\nLIMIT 100;"
  }
]
7

EXPLAINs look good.

If i matches a huge number of documents, and j matches very few, the INTERSECT will be more efficient.

8

Hi @m0thra,

In the upcoming version of couchbase where
Loading... bug has been fixed, you can use the query as:
SELECT r AS doc, meta(r).cas AS revision FROM record AS r WHERE any i in object_pairs(indexMap)satisfies i = { “name”:“key1”, “val”:“val1”} end AND any i in object_pairs(indexMap)satisfies i = { “name”:“key2”, “val”:“val2”} end LIMIT 100

Since we have changed the output of object_pairs and object_inner_pairs to contain name and val instead of name and value pairs.

Thanks
Prerna

1 Like