在Oracle的json对象数组中添加末尾的元素

Question

我有一个json列名为" configuration"在Oracle数据库中使用-

{"sections":[{"active":true,"code":"page.about"},{"active":true,"code":"page.title"}...]}

如何将元素添加到"节"&;数组中的CLOB?例如，将此对象添加到CLOB- {"active":false， "code":"page.body"}

I tried to do this-

UPDATE *TABLE_NAME*
SET configuration = JSON_MODIFY(configuration, 'append $.sections',JSON_QUERY(N'{"active":false,"code":"page.body"}'))

但是我得到了这个错误-错误报告-SQL错误:ORA-00917:缺少逗号00917. 00000 -"缺少逗号";*原因:
*行动:

谢谢!

Answer 1

您可以创建以下函数:

CREATE FUNCTION json_append_array(
json  IN CLOB,
path  IN VARCHAR2,
value IN CLOB
) RETURN CLOB
IS
j_obj JSON_OBJECT_T := JSON_OBJECT_T(json);
j_arr JSON_ARRAY_T  := j_obj.get_Array(path);
BEGIN
j_arr.append( JSON_OBJECT_T(value) );
RETURN j_obj.to_Clob();
END;
/

然后你可以更新表:

UPDATE TABLE_NAME
SET configuration = JSON_APPEND_ARRAY(
configuration,
'sections',
'{"active":false,"code":"page.body"}'
);

:

SELECT *
FROM   table_name;

输出:

tbody> <<tr>

CONFIGURATION
{"sections"[{"active"真的,"code":"page.about"},{"active"真的,"code":"page.title"},{"active"假的,"code":"page.body"}]}

您可以将数组拆分为行，使用UNION ALL添加另一行并重新聚合，使用JSON_MERGEPATCH更新对象:

MERGE INTO table_name dst
USING (
SELECT t.ROWID AS rid,
a.new_value
FROM   table_name t
CROSS JOIN LATERAL (
SELECT JSON_OBJECT(
KEY 'sections' VALUE JSON_ARRAYAGG(value FORMAT JSON)
) AS new_value
FROM   (
SELECT value
FROM   JSON_TABLE(
t.configuration,
'$.sections[*]'
COLUMNS value CLOB FORMAT JSON PATH '$'
)
UNION ALL
SELECT EMPTY_CLOB() || '{"active":false,"code":"page.body"}' FROM DUAL
)
) a
) src
ON (dst.ROWID = src.rid)
WHEN MATCHED THEN
UPDATE
SET configuration = JSON_MERGEPATCH(dst.configuration, src.new_value);

对于样本数据:

CREATE TABLE table_name (configuration CLOB CHECK (configuration IS JSON));
INSERT INTO table_name ( configuration )
VALUES ('{"sections":[{"active":true,"code":"page.about"},{"active":true,"code":"page.title"}]}');

然后，在merge语句之后:

SELECT *
FROM   table_name;

输出:

tbody> <<tr>

CONFIGURATION
{"sections"[{"active"真的,"code":"page.about"},{"active"真的,"code":"page.title"},{"active"假的,"code":"page.body"}]}