WHERE BETWEEN子句中的PostgreSQL jsonb值 [英] PostgreSQL jsonb value in WHERE BETWEEN clause

问题描述

我的数据库表(a_table)中有jsonb字段，其中包含int值，例如:

I've got jsonb field in my database table (a_table) with int value within, say:

{
  "abc":{
       "def":{
            "ghk":500
        }
   }
}

我将使用WHERE子句通过此字段("ghk")创建带有过滤器的SELECT:

I'm about to create SELECT with filter by this field ("ghk") using WHERE clause:

SELECT * FROM a_table WHERE ghk BETWEEN 0 AND 1000;

我应该如何创建这样的查询?到目前为止，找不到有关jsonb使用的好的教程.

How should i create such a query? Couldn't find good tutorial for jsonb usage so far.

提前谢谢！

编辑 我找到了此解决方案:

EDIT I found this solution:

SELECT * FROM a_table WHERE a_field #> '{abc,def,ghk}' BETWEEN '0' AND '10000' ;

对吗?

Is it correct?

推荐答案

#>返回一个无法转换为int的JSONB文档.您需要#>>运算符，该运算符返回可以转换为整数的标量值:

The #> returns a JSONB document which you cannot cast to an int. You need the #>> operator which returns a scalar value that can be casted to an integer:

select *
from a_table
where (json_col #>> '{abc,def,ghk}')::int between 0 and 1000

手册中记录了所有JSON运算符: http://www .postgresql.org/docs/current/static/functions-json.html

All JSON operators are documented in the manual: http://www.postgresql.org/docs/current/static/functions-json.html

使用BETWEEN '0' AND '10000'并不是一个好主意，因为这会执行字符串比较而不是数字比较.值'2'不在'0'和'10000'之间.这就是为什么您需要将返回值转换为数字以获得正确的比较.

Using BETWEEN '0' AND '10000' is not a good idea because this does a string comparison not a numeric comparison. The value '2' does not lie between '0' and '10000'. That's why you need to cast the returned value to a number to get a correct comparison.

这篇关于WHERE BETWEEN子句中的PostgreSQL jsonb值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！