将jsonb_each的输出保存在物化视图中

Question

我在第12页有张桌子-

Id int

Col1 character varying

Data_col jsonb

Select t.id, t.col1, x.key, x.value
From t, jsonb_each_text(t.data_col) x

Create materialized view t_mv
As
Select t.id, t.col1, x.key, x.value
From t, jsonb_each_text(t.data_col) x;

error cannot call jsonb_each on a non-object

谢谢

Answer 1

t.data_col中的值之一必须是数组或标量：

SELECT * FROM jsonb_each_text('"scalar"'::jsonb);
ERROR:  cannot call jsonb_each_text on a non-object

SELECT * FROM jsonb_each_text('[1,2,3]'::jsonb);
ERROR:  cannot call jsonb_each_text on a non-object

要查找有问题的行，可以使用如下查询

SELECT id FROM t WHERE jsonb_typeof(data_col) <> 'object';