使用json_table中的变量值

Question

我有以下json:

{
"title": "title",
"description": "description",
"references": [{
"reference_id": 181
},
{
"reference_id": 182
}]
}

我想在包含以下列的表xyz中插入引用数组的数据：buffer_id和reference_id

INSERT INTO xyz (
buffer_id,
reference_id
)
SELECT
l_buffer_id,
reference_id
FROM
dual,
JSON_TABLE ( data, '$.references[*]'
COLUMNS (
l_buffer_id ,
reference_id NUMBER PATH '$.reference_id'
)
);

这里，l_buffer_id是存储我的值的变量。我尝试了上面的方法将数据插入xyz。然而，它不起作用。有什么办法让它发挥作用吗？

Answer 1

您可以使用这样的机制：

INSERT INTO xyz (
buffer_id,
reference_id
)
WITH tab(data) AS
(
SELECT '{"title": "title",
"description": "description",
"references": [{
"reference_id": 181
},
{
"reference_id": 182
}]
}' FROM dual
)
SELECT row_number() over (order by reference_id) as l_buffer_id,
reference_id
FROM tab,
json_table(data, '$'
COLUMNS (NESTED PATH '$."references"[*]'
COLUMNS (reference_id NUMBER PATH '$."reference_id"')));  

演示