比较postgres的jsonb字段中的数值

Question

我在一个名为meta的类型为jsonb的postgresql数据库表中有一列。这是我在表的元列中的一行中的数据。

{
budget: {
data: "2018-12-15",
target: 47.5,
spend: 12.3
} 
}

我正在尝试编写一个查询，返回所在的所有行

meta.budget.spend < meta.budget.target 

或

meta.budget.date != "2018-12-15"

我试过

SELECT ... WHERE (("table"."meta"#>>'{budget,spend}') < "table"."meta"#>>'{budget,target}'
OR ("table"."meta"#>>'{budget,date}') != '2018-12-15')

结果我得到了0行。我在查询，对吗？如果没有，我该如何修复。

Answer 1

您可以使用：

SELECT *
,(meta->'budget'->'target')::text::numeric(10,2) AS target
,(meta->'budget'->'spend')::text::numeric(10,2) AS spend
FROM tab
WHERE (meta->'budget'->'spend')::text::numeric(10,2) < 
(meta->'budget'->'target')::text::numeric(10,2)
OR (meta->'budget'->'data')::text::date != '2018-12-15'::date

db<>小提琴演示

Answer 2

您的问题是#>>会给您text:

运算符:#>>
右操作数类型.text[]
在指定路径获取JSON对象作为文本

和text不一定会以您想要的方式进行比较。如果你添加几个类型的投射，你会得到更好的结果：

("table"."meta" #>> '{budget,spend}')::numeric < ("table"."meta" #>> '{budget,target}')::numeric
OR ("table"."meta" #>> '{budget,date}')::date != '2018-12-15'

如果你想显式的话，你也可以说'2018-12-15'::date，但这是左手边的date所暗示的。