Oracle 19c中现有JSON的JSON_ARRAY追加及排序问题

需求说明 #

需要将存储在tbl3表中的已有JSON文档内的values数组，追加来自tbl2的新元素，最终得到如下结构的JSON：

额外需求：能否按values数组中每个子数组的第一个元素（对应VAL1）对数组进行排序？

环境准备SQL #

CREATE TABLE tbl1 (val1 varchar2(10), val2 varchar2(10), val3 varchar2(10));
CREATE TABLE tbl2 (val1 varchar2(10), val2 varchar2(10), val3 varchar2(10));
CREATE TABLE tbl3 (json clob, json_updated clob);
INSERT INTO tbl1 VALUES ('1','2','3');
INSERT INTO tbl1 VALUES ('a','b','c');
INSERT INTO tbl1 VALUES ('1','b','3');
INSERT INTO tbl2 VALUES ('2','d','f');
INSERT INTO tbl2 VALUES ('3','b','g');

-- 初始化tbl3的JSON数据
insert into tbl3
select json_object(
'keys' : ['VAL1', 'VAL2', 'VAL3'],
'values' : json_arrayagg(json_array(val1, val2, val3 null on null))) as js, null
from tbl1;

初始化后tbl3中的JSON数据：

tbl2转换后的JSON结构：

实现方案 #

1. 更新tbl3中的JSON文档 #

基于你已实现的合并查询，直接构建完整JSON并更新表字段：

UPDATE tbl3 t
SET json_updated = JSON_OBJECT(
    'keys' : ['VAL1', 'VAL2', 'VAL3'],
    'values' : (
        SELECT JSON_ARRAYAGG(json)
        FROM (
            SELECT json
            FROM JSON_TABLE(t.json, '$.values[*]' COLUMNS (json CLOB FORMAT JSON PATH '$'))
            UNION ALL
            SELECT JSON_ARRAY(val1, val2, val3 null on null returning clob)
            FROM tbl2
        )
    )
);

执行后查询json_updated字段即可得到目标结构的JSON。

2. 按子数组第一个元素排序 #

在JSON_ARRAYAGG中添加排序规则，解析子数组第一个元素作为排序依据：

UPDATE tbl3 t
SET json_updated = JSON_OBJECT(
    'keys' : ['VAL1', 'VAL2', 'VAL3'],
    'values' : (
        SELECT JSON_ARRAYAGG(json ORDER BY JSON_VALUE(json, '$[0]'))
        FROM (
            SELECT json
            FROM JSON_TABLE(t.json, '$.values[*]' COLUMNS (json CLOB FORMAT JSON PATH '$'))
            UNION ALL
            SELECT JSON_ARRAY(val1, val2, val3 null on null returning clob)
            FROM tbl2
        )
    )
);

排序后values数组会按子数组第一个元素的字符顺序排列：["1","2","3"],["1","b","3"],["2","d","f"],["3","b","g"],["a","b","c"]

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