我想知道是否有办法在Oracle中格式化JSON(就像这个网站的示例一样)。
在XML中,我使用了:
SELECT XMLSERIALIZE(Document XMLTYPE(V_RESPONSE) AS CLOB INDENT SIZE = 2)
INTO V_RESPONSE
FROM DUAL;
而且它运作得非常好。
RETURNING ... PRETTY
子句中使用JSON_QUERY()
函数:
PRETTY
:指定PRETTY
以通过插入换行符和缩进来使返回字符串更易读
表达式:
JSON_QUERY(js_value, '$' RETURNING VARCHAR2(4000) PRETTY)
with t as (select '{"a":1, "b": [{"b1":2}, {"b2": "z"}]}' js from dual)
select json_query(js, '$' returning varchar2(4000) pretty) pretty_js, js from t;
收益率:
PRETTY_JS | JS
--------------------------|----------------------------------------
{ | {"a":1, "b": [{"b1":2}, {"b2": "z"}]}
"a" : 1, |
"b" : |
[ |
{ |
"b1" : 2 |
}, |
{ |
"b2" : "z" |
} |
] |
} |
这使您能够在VARCHAR2/CLOB/BLOB之间转换JSON。并包含PRETTY子句:
with t as (
select '{"a":1, "b": [{"b1":2}, {"b2": "z"}]}' js
from dual
)
select json_serialize (
js returning varchar2 pretty
) pretty_js,
js
from t;
PRETTY_JS JS
{ {"a":1, "b": [{"b1":2}, {"b2": "z"}]}
"a" : 1,
"b" :
[
{
"b1" : 2
},
{
"b2" : "z"
}
]
}