postgresql 为什么不能直接在jsonb_array_elements上查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30687945/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why can't I query directly on jsonb_array_elements?
提问by Sami Kuhmonen
I have data stored as jsonb in a column called "data":
我将数据作为 jsonb 存储在名为“data”的列中：
{'people': [{"name": "Bob", "Occupation": "janitor"}, {"name": "Susan", "Occupation", "CEO"}]}
I can query this via:
我可以通过以下方式查询：
SELECT mydata.pk FROM mydata, jsonb_array_elements(mydata.data->'people') AS a WHERE (a->>'name') = 'bob'
Why can't I substitute "a" for the jsonb_array_elements(...)?:
为什么我不能用“a”代替 jsonb_array_elements(...)？：
SELECT mydata.pk FROM mydata WHERE (jsonb_array_elements(mydata.data->'people')->>'name') = 'bob'
Instead, I get the following:
相反，我得到以下信息：
ERROR: argument of WHERE must not return a set
回答by Sami Kuhmonen
As the error message says, arguments to WHEREmust not return a set. jsonb_array_elementsreturns a set and it can't be compared to a single value. In the second query you have a cross join inside the select and that converts it into a suitable result to use WHEREon.
正如错误消息所说，参数WHERE不能返回一个集合。jsonb_array_elements返回一个集合，它不能与单个值进行比较。在第二个查询中，您在 select 中有一个交叉联接，并将其转换为合适的结果以供使用WHERE。
You can also do it this way
你也可以这样做
SELECT mydata.pk FROM mydata
WHERE 'Bob' in (SELECT jsonb_array_elements(mydata.data->'people')->>'name');
Here the subselect will allow you to use the INoperator to find the desired value since the result is no longer a set.
在这里，子选择将允许您使用IN运算符来查找所需的值，因为结果不再是一个集合。
Another way is to query the jsonb directly
另一种方式是直接查询jsonb
SELECT mydata.pk FROM mydata
WHERE mydata.data->'people' @> '[{"name":"Bob"}]'::jsonb;
This way you don't need to convert the jsonb into a resultset and search within it.
这样您就不需要将 jsonb 转换为结果集并在其中进行搜索。
