Oracle和PostgreSQL中的转义方括号

Question

我正在做一个项目，其中的数据库可以是oracle或Postgres的实例。

我需要写一个类似的查询，这两个数据库上的工作。该查询处理包含JSON字符串的text列，例如：

{"ruleName":"r2_an","divisionNameList":["div1"],"names":["name1"],"thirdTypeLabels":[],"secondTypeLabels":[],"firstTypeLabels":[]}

我需要选择thirdTypeLabels为空的行。

select *
from my_table
where JSON like '%thirdTypeLabels%[]%';

例如，在Oracle上，不提取任何内容，即使在"my_table“中有多行匹配也是如此。

查询在Java软件中，使用JDBC，因为我们需要性能。

你有什么建议吗？

Answer 1

对于Postgres，您有两个选择来使此查询正常工作：

select *
from the_table
where jsonb_array_length(json::jsonb -> 'thirdTypeLabels') = 0;

或者-从Postgres 12开始-使用JSON路径表达式

select *
from the_table
where jsonb_path_exists(json::jsonb, '$.thirdTypeLabels.size() ? (@ == 0)' );

或者使用与Oracle中相同的JSON路径表达式：

select *
from the_table
where jsonb_path_exists(json::jsonb, '$' ? (@.thirdTypeLabels.size() == 0)');

在Postgres中，您还应该使用定义为jsonb而不是text (或varchar)的列。

Answer 2

您应该使用适当的thirdTypeLabels解析器，否则不能保证%thirdTypeLabels%[]%将限制空数组与JSON键值对的匹配。

因此，对于Oracle 18c，您可以使用：

SELECT id,
       thirdTypeLabelsCount
FROM   mytable t
       CROSS JOIN
       JSON_TABLE(
         t.json,
         '$'
         COLUMNS(
           thirdTypeLabelsCount NUMBER PATH '$.thirdTypeLabels.size()'
         )
       )
WHERE  thirdTypeLabelsCount = 0;

或

SELECT *
FROM   mytable
WHERE  JSON_EXISTS( json, '$ ? (@.thirdTypeLabels.size() == 0) ' )

db<>fiddle