Oracle Json Functions

JSON_ARRAY

JSON_ARRAY('[1,2,3]', '{"x":1}' format json)
JSON_ARRAY(1，2, null, 3) 
JSON_ARRAY(1，2, null, 3 NULL ON NULL) 
JSON_ARRAY(1，2, null, 3 ABSENT ON NULL)

JSON_ARRAYAGG

SELECT name,json_arrayagg(hobby ORDER BY hobby) FROM q1 GROUP BY name

json_object

json_object(KEY 'a' value 1)
json_object('a' : '1' format json)
SELECT json_object(*) FROM table;

JSON_OBJECTAGG

SELECT  json_objectagg(KEY col1 value col2 WITH UNIQUE keys)  FROM

Json_query

JSON_QUERY('{a:100, b:200, c:300}', '$')
JSON_QUERY('{a:100, b:200, c:300}', '$.a' WITH WRAPPER) 
-- 路径表达式匹配标量值，必须用数组包装器括起来
JSON_QUERY('{a:100, b:200, c:300}', '**$.***' WITH WRAPPER)
-- 路径表达式匹配多个值，这些值必须一起用数组包装器括起来
JSON_QUERY('[0,1,2,3,4]', '$') AS value
-- 路径表达式匹配单个 JSON 数组，不需要数组包装器
JSON_QUERY('[0,1,2,3,4]', '$[*]' WITH WRAPPER)
-- 路径表达式匹配多个值，这些值必须一起用数组包装器括起来

JSON_QUERY('[0,1,2,3,4,5,6,7,8]', '$[0, 3 to 5, 7]' WITH WRAPPER)
JSON_QUERY('[0,1,2,3,4]', '$[3]' WITH WRAPPER)

JSON_QUERY('[{a:100},{b:200},{c:300}]', '$[0]' WITH CONDITIONAL WRAPPER
-- {"a":100}
JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*****]' WITH CONDITIONAL WRAPPER)
-- [{"a":100},{"b":200},{"c":300}]

JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[3]' EMPTY ON ERROR)