我什么时候应该在N1ql中使用UNNEST与ANY ... SATISFIES? [英] When should I use UNNEST vs ANY...SATISFIES in N1ql?

问题描述

我想查询(或索引)一个数组值的字段.

I want to query (or index) an array-valued field.

例如，假设我要检索此文档 {"myarray":[1，2，3]} .

As an example, say I want to retrieve this document { "myarray": [ 1, 2, 3]}.

我可以做到这一点 ANY ... SATISFIES 或使用 UNNEST .在文档中，这些似乎功能上相同.

I can do this with ANY...SATISFIES or with UNNEST. From the documentation, these seem functionally the same.

SELECT *从`bucket`和myarray中的任何v满足v = 3 END;

SELECT * FROM`bucket`未知myarray v WHERE v = 3

每种情况都有哪些用例?

What are the use cases for each?

推荐答案

对于这两个查询，它们执行相似的操作，但是这两种方法都提供了其他功能.

For those two queries, they do similar things, but both of these approaches provide other functionality.

这两个查询的实际结果应该不同.第一个查询将按原样返回数组数据，而UNNEST将平整数组.

The actual results of both those queries should be different. The first query will return the array data as is, while UNNEST will flatten the array.

UNNEST是文档内联接.SATISFIES允许您(已完成)检查数组以查看其是否满足某些条件，但实际上并不会以任何方式对数组进行转换.

UNNEST is an intra-document join. SATISFIES allows you to (as you've done), check an array to see if it meets some criteria, but it doesn't actually transform the array in the results in any way.

更新:

不一定是哪个更好"的问题.这两个查询都做不同的事情.假设您的文档如下所示:

It isn't necessarily a matter of 'which is better'. Both of these queries do different things. Let's suppose your document looks like this:

{
  "foo": "bar",
  "myarray": [
    1,
    2,
    3
  ]
}

现在让我们假设您从这两个查询中都删除了 WHERE .

Now let's suppose you remove the WHERE from both of those queries.

然后，运行此查询:

SELECT d.foo, d.myarray, v
FROM `demo` d
UNNEST d.myarray v

您将获得3个结果，因为正在进行联接.像这样:

You get 3 results, because a join is taking place. Like this:

[
{"foo":"bar","myarray":[1,2,3],"v":1},
{"foo":"bar","myarray":[1,2,3],"v":2},
{"foo":"bar","myarray":[1,2,3],"v":3}
]

与其他查询:

SELECT d.*
FROM `demo` d

您将得到一个结果，因为没有加入发生.这是文档内谓词，但不是文档内联接.

You get one result, because there is no join happening. It's an intra-document predicate, but not an intra-document join.

[{"foo":"bar","myarray":[1,2,3]}]

要使用哪个?一般来说，这取决于您的用例.Stack Overflow并非用于提供此类特定于激光器的建议.如果您只是追求速度，我建议对您的真实数据进行测试，以查看哪种效率更高(您的样本文档可能不是您的真实文档).

As to which one to use? It depends on your use case, generally speaking. Stack Overflow isn't for giving such laser-specific advice. If you're merely after speed, I would recommend testing both on your real data, to see which is more efficient (your sample document probably isn't your real document).

建立索引也是一个因素.同样，仅基于示例文档，对于SATISFIES查询，您可能会创建一个如下所示的索引:

Indexing is also a factor. Again, based only on your sample document, for the SATISFIES query, you'd probably create an index like this:

CREATE INDEX adv_DISTINCT_myarray ON `demo`(DISTINCT `myarray`)

对于UNNEST查询，您可能会创建一个像这样的索引:

And for the UNNEST query, you'd probably create an index like this:

CREATE INDEX adv_ALL_myarray ON `demo`(ALL `myarray`)

这些索引假定您要做的只是检查 myarray 中的单个值.如果您的实际查询更复杂，那么您将需要一个更复杂的索引.

Those indexes assume that all you're doing is checking myarray for a single value. If your real queries are more complex, you'll need a more complex index.

另外一个注意事项:在幕后，在查询引擎中，我不知道实现的区别是什么，因此我不得不接受Johan的建议，即UNNEST的价格更高.但是您的里程可能会有所不同，所以我建议您同时尝试并做一些基准测试.

One additional note: Behind the scenes, in the query engine, I have no idea what the implementation difference is, so I would have to go with Johan's advice on the UNNEST being more expensive. But your mileage may vary, so I would recommend trying both and doing some benchmarks.

这篇关于我什么时候应该在N1ql中使用UNNEST与ANY ... SATISFIES?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！