运算符不存在: json @>未知

Question

我有下表，我需要布局名称，其userid='user-1‘和gridname='RT’

| userid |                                                                              defaultdata |
|--------|------------------------------------------------------------------------------------------|
| user-1 | [{"gridname":"RT", "layoutname":"layout-1"},{"gridname":"RT2", "layoutname":"layout-2"}] |
| user-2 | [{"gridname":"RT", "layoutname":"layout-3"},{"gridname":"RT2", "layoutname":"layout-2"}] |

到目前为止我已经试过了。

 SELECT userid, obj.value->>'gridname' AS gridname
 FROM   col.userdefault t
 JOIN   lateral jsonb_array_elements(t.defaultdata::jsonb) obj(value) ON 
 obj.value->>'_gridname' = 'RT'
 WHERE  defaultdata @> '[{"_gridname":"RT"}]';

但不工作，并得到以下错误：

ERROR:  operator does not exist: json @> unknown
LINE 4: WHERE  defaultdata @> '[{"_gridname":"RT"}]::jsonb';
                           ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 198

Answer 1

@>运算符不存在于json，而仅存在于jsonb。

您可以使用显式类型强制转换来消除即时错误，正如错误消息建议的那样：

WHERE defaultdata::jsonb @> '...'

但是，从更远的角度来看，我想知道json是否是适合您的数据类型。对于您想要有效地在数据库中操作的数据，jsonb更好。

Answer 2

demo: db<>fiddle

要获得网格名的值，必须展开数组，然后检查其中的每个元素：

SELECT
    userid,
    elems ->> 'layoutname' AS layoutname                  -- 3
FROM
    mytable,
    json_array_elements(defaultdata) elems                -- 1
WHERE userid = 'user-1' AND elems ->> 'gridname' = 'RT'   -- 2

1. json_array_elements()将JSON数组扩展为每一个元素的一行。
2. 展开后的元素可以检查其相关值，并可用作筛选器。
3. 之后，您就可以获得数据了。

我在玩@>比较器。我发现您的方式不错(假设您使用的是jsonb类型而不是json类型)。@>可以完成以下工作：

SELECT 
    *
FROM mytable
WHERE defaultdata @> '[{"gridname":"RT"}]'

演示

这将返回整个数组，而不仅仅是包含该数组的元素。这意味着，如果以这种方式使用它，它将返回数组，其中任何元素都包含此部分。

要得到一个元素，您需要扩展它，您已经做了。唯一的问题是，您没有在WHERE子句中使用展开元素，而是使用原始数据。使用展开元素将导致此查询：

SELECT 
    userid,
    elems ->> 'layoutname' AS layoutname
FROM mytable,
    jsonb_array_elements(defaultdata) elems
WHERE userid = 'user-1' AND elems @> '{"gridname":"RT"}'

演示

而且这个很好！