database - 如何比较 PostgreSQL JSONB 中的数字

Question

我在使用 jsonb 类型时遇到了奇怪的情况。

预期行为

---

使用简短的jsonb结构:

{"price": 99.99}

我这样写查询:

 SELECT * FROM table t WHERE t.data->price > 90.90

它失败并显示错误 operator does not exist: jsonb > numeric the same as text (->>>) operator does not exist: text > numeric

然后我写了很多资源中提到的比较:

SELECT * FROM table t WHERE (t.data->>price)::NUMERIC > 90.90

它按预期工作。

有什么奇怪的:

---

SELECT * FROM table t WHERE t.data->price > '90.90';

有点奇怪，但上面的查询是正确的。

解释: 过滤器:((data -> 'price'::text) > '90.90'::jsonb)

但是如果我将 jsonb 值更改为 text 为:{"price": "99.99"}不再有结果 - 空的。

问题:PostgreSQL 实际上是如何比较数字数据的，以及进行这种比较的最佳方法是什么。

Answer 1

但您不是在比较数字数据，对吧。

我可以看出您认为 price 包含一个数字，但它没有。它包含一个 JSON 值。那可能是一个数字，或者它可能是文本，或者一个数组，或者一个对象，或者一个包含对象数组的对象，这些对象包含......

你可能会说“但是这个键叫做‘价格’，当然它是一个数字”但是这对 PostgreSQL 没有用，特别是如果我来偷偷插入一个包含对象数组的对象...... ¹

所以 - 如果你想比较一个数字，你需要将它转换为数字 (t.data->>price)::NUMERIC 或将你的目标值转换为 JSON 并让 PostgreSQL 做基于 JSON 的比较(可能做你想做的事，也可能不会 - 我不知道 JSON 的确切规则是什么)。

---

¹ 这正是我会做的事情，即使是圣诞节。我是个坏人。