为什么我不能直接在jsonb

本文介绍了为什么我不能直接在jsonb_array_elements上查询?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我将数据以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.