Oracle SQL多函数构建JSON时数组转为字符串的问题排查

问题根源 #

你调用返回CLOB类型的函数get_json_indicadores_op_plano(id_pr)时，Oracle会将CLOB中的JSON字符串视为普通文本，自动添加双引号转义，导致本该是JSON数组的部分变成了字符串值。

解决方案 #

方案1：用JSON_QUERY解析函数返回的CLOB #

在主JSON生成语句中，用JSON_QUERY包裹函数调用，明确告诉Oracle这是合法的JSON内容，无需转义：

KEY 'indicadoresOperacao' VALUE JSON_QUERY(get_json_indicadores_op_plano(id_pr), '$')

'$'表示取函数返回的整个JSON内容，这样Oracle会将CLOB解析为JSON数组而非字符串。

方案2：让函数返回JSON_TYPE类型（Oracle 12cR2及以上支持） #

修改函数的返回类型为JSON_TYPE，替代CLOB。Oracle会直接识别该类型为JSON，不会做字符串转义处理：

FUNCTION get_json_indicadores_op_plano(p_id_pr NUMBER) RETURN JSON_TYPE IS
  v_json JSON_TYPE;
BEGIN
  SELECT JSON_ARRAYAGG(
           JSON_OBJECT(
             'id' VALUE id,
             'indicadorConceitoSincronizado' VALUE indicador_conceito_sincronizado,
             'indicadorSincronizarConceito' VALUE indicador_sincronizar_conceito
           )
         ) INTO v_json
  FROM your_table_name
  WHERE id_pr = p_id_pr;
  RETURN v_json;
END;

主JSON生成时直接调用函数即可：

KEY 'indicadoresOperacao' VALUE get_json_indicadores_op_plano(id_pr)

注意事项 #

如果选择方案1，必须确保函数生成的CLOB是合法的JSON格式（比如示例中缺失的闭合}]要补全），否则JSON_QUERY会抛出语法错误。

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