我将数据以jsonb的形式存储在名为数据"的列中:
I have data stored as jsonb in a column called "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'为什么我不能用"a"代替jsonb_array_elements(...)?:
Why can't I substitute "a" for the 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推荐答案
如错误消息所述,WHERE的参数不得返回集合. jsonb_array_elements返回一个集合,不能将其与单个值进行比较.在第二个查询中,select内有一个交叉联接,并将其转换为合适的结果以使用WHERE on.
As the error message says, arguments to WHERE must not return a set. jsonb_array_elements returns 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 WHERE on.
您也可以这样
SELECT mydata.pk FROM mydata WHERE 'Bob' in (SELECT jsonb_array_elements(mydata.data->'people')->>'name');在这里,子选择将允许您使用IN运算符来查找所需的值,因为结果不再是一个集合.
Here the subselect will allow you to use the IN operator to find the desired value since the result is no longer a set.
另一种方法是直接查询jsonb
Another way is to query the jsonb directly
SELECT mydata.pk FROM mydata WHERE mydata.data->'people' @> '[{"name":"Bob"}]'::jsonb;这样,您无需将jsonb转换为结果集并在其中搜索.
This way you don't need to convert the jsonb into a resultset and search within it.
更多推荐
为什么我不能直接在jsonb
发布评论