'cannot extract element from a scalar'当按 id 排序为 DESC 时

Question

我有一个PostgreSQL 9.3.10数据库，一个有15842条记录的表，并使用Intellij进行查询。我想从JSON列(名为"data"(中查询一些最近的数据。

我的问题很简单：

select data->'header'
from some_table
order by id desc

问题是我得到了：

[22023]错误：无法从标量中提取元素

当我使用desc顺序时。当我使用asc时，查询不会失败，并且至少接收到前500条记录。

对我来说，最近的一些记录可能有无效的JSON。如果是，我如何忽略坏记录，仍然使用DESC排序执行查询？或者如何识别损坏的JSON记录？

我还认为它可能是由空数据引起的，但select * from table where data is null没有显示任何结果。

UPD:"data"列的类型为json。我发现有些行将json数据序列化为字符串(因为后端有故障(。因此，值不是普通的json对象:{}，而是一个带引号的对象字符串"{}"。通过以下查询检测到：select data from some_table where data::text not like '{%'

Answer 1

您对问题的描述表明data列不是用正确的数据类型创建的。如果是json或jsonb，则在插入时错误数据将被拒绝。

我不认为Postgres有一个内置的函数来检查JSON字符串的有效性。你需要创建一个，比如：

create or replace function is_jsonb(js text)
returns boolean
as $$
begin
return (js::jsonb is not null);
exception 
when others then return false;  
end;
$$
language plpgsql;

然后您可以在查询中使用它：

select *
from mytable
where not is_jsonb(data);

根据data的数据类型，可能需要额外的强制转换，如：

where not is_jsonb(data::text);

Answer 2

问题是由ActiveRecord版本更新引起的，该版本更新导致显式接收to_json的Ruby对象被写为单个字符串，该字符串也是有效的JSON数据(Postgres在错误消息中称其为"标量"(。这就是为什么只有在使用by id desc的查询中才会出现错误(仅适用于ActiveRecord更新后创建的新记录(。以下是关于ActiveRecord更改的说明：https://github.com/rails/rails/commit/835246e622dc0431af3cb951db22ef78876006af

因此，它被写成"{"header": "value"}"，而不是{"header": "value"}。数据库已通过以下查询修复：

update some_table 
set data = REPLACE(TRIM(BOTH '"' from data::text), '"', '"')::JSON 
where data::text like '"%';