如何在Oracle中获取存储为字符串的JSON数组的长度

现有表结构与测试数据 #

create table docs(id number, doc json);
insert into docs values(1,'{
    "name":"Jay",
    "arr1":[1,2,3,4,5],
    "arr2":"[1,2,3,4,5]"
}');

问题说明 #

表中arr1是原生JSON数组，使用json_array_length(doc, '$.arr1')能正确返回长度5；但arr2是以字符串形式存储的数组，直接调用相同函数会返回1（因为它被识别为单个字符串值），需要获取其实际数组长度5。

解决方案 #

方法一：通过json_parse转换字符串为JSON数组 #

select 
  json_array_length(doc, '$.arr1') as arr1_length,
  json_array_length(json_parse(json_value(doc, '$.arr2'))) as arr2_length
from docs;

方法二：使用format json修饰路径表达式 #

select 
  json_array_length(doc, '$.arr1') as arr1_length,
  json_array_length(doc, '$.arr2' format json) as arr2_length
from docs;

核心逻辑说明 #

• json_parse：将符合JSON格式的字符串转换为JSON数组类型，让Oracle能够识别其数组结构。
• format json：在路径表达式中添加该修饰符，直接告知Oracle将目标字符串当作JSON数据处理，无需额外转换步骤。

内容的提问来源于stack exchange，提问作者Srijay