sql—“当order by id为desc时，无法从标量中提取元素”

k75qkfdt 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(610)

我有一个postgresql9.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 '{%'

sql postgresql JSON where-clause postgresql-9.3

来源：https://stackoverflow.com/questions/63069724/cannot-extract-element-from-a-scalar-when-order-by-id-is-desc

2条答案

按热度按时间

lrpiutwd1#

你对问题的描述表明 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);

赞(0）回复(0）举报 2021-07-24

k5hmc34c2#

问题是由activerecord版本更新引起的，该更新导致ruby对象接收到 to_json 显式地写为一个字符串，它也是一个有效的json数据（postgres在错误消息中称之为：“scalar”）。这就是为什么错误只出现在使用 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 '"%';

赞(0）回复(0）举报 2021-07-24

我来回答

sql—“当order by id为desc时，无法从标量中提取元素”

2条答案

相关问题

热门标签

最新问答