查询不同类型的PostgreSQL JSONB列的数据

Question

作为我的PostgreSQL DB模式的一部分，目前运行版本11，但如果它解除阻塞，我愿意升级:我有一个jsonb列data，它包含各行中各种结构的嵌套对象，我无法控制。例:

第一行可能是:{'rootProperty': { 'nestedProperty': 'someStrVal' }}

和第2行可能有类似的模式:{'rootProperty': { 'nestedProperty': 2, 'otherNestedProperty': 'someOtherString' }}

当尝试根据jsonb列中具有不同类型的属性查询行/行子集时，我的困难就来了。在本例中，nestedProperty在第1行是字符串，在第2行是int。

当我尝试运行诸如

之类的查询时SELECT * FROM TABLE WHERE data -> 'rootProperty' ->> 'nestedProperty' = 'someStrVal'

一切正常，但如果我尝试

SELECT * FROM TABLE WHERE data -> 'rootProperty' ->> 'nestedProperty' > 1

或

SELECT * FROM TABLE WHERE (data -> 'rootProperty' ->> 'nestedProperty')::int > 1

查询错误，'操作符不存在:text>

是否有一种方法，我可以有jsonb列与可变模式，可能有重叠的结构，虽然有不同的数据类型，仍然查询他们所有?我不介意指定我要查找的类型，只要它可以跳过或绕过不符合该类型标准的行。

Answer 1

为同一属性设置不同类型的值一开始似乎很奇怪，但很可能你无法改变这种"设计"。

Postgres 12引入了对SQL/JSON路径表达式的支持，这对数据类型转换很宽容，并且如果你试图将someStrVal与数字进行比较，也不会出错。

查询:

select *
from the_table
where data @@ '$.rootProperty.nestedProperty > 1'

将返回nestedProperty为有效数字且大于1的所有行。不能转换为数字的值将被静默忽略。

也可以写成:

where jsonb_path_exists(the_column, '$.rootProperty.nestedProperty ? (@ > 1)')

---

该值可以通过使用jsonb_path_exists()和第三个参数作为参数传递:

where jsonb_path_exists(the_column, '$.rootProperty.nestedProperty ? (@ > $nr)', '{"nr": 1}')

最后一个参数可以通过参数占位符传递，例如在Java中:

where jsonb_path_exists(the_column, '$.rootProperty.nestedProperty ? (@ > $nr)', cast(? as jsonb))

然后:

PreparedStatement pstmt = conn.prepareStatement(...);
pstmt.setString(1, "{"nr": 1}");

Answer 2

我不记得具体是哪个版本引入的，但是您可以使用json_typeof函数和CASE表达式将属性的值转换为正确的类型。我会使用SQL函数，以保持我的查询所有整洁:

CREATE FUNCTION jsonb_to_integer(jsonb, text) RETURNS integer AS
$$
SELECT CASE jsonb_typeof($1->$2) 
WHEN 'number' THEN ($1->>$2)::integer 
ELSE null 
END 
$$
LANGUAGE SQL
STABLE
RETURNS NULL ON NULL INPUT;

然后只是:

SELECT * FROM TABLE WHERE jsonb_to_integer(data, 'rootProperty') > 1;