如何使用 Oracle 12c 查询为 JSON 中的数组编制索引

Question

我有一个表格"move"，其中一列"move_doc"是一个CLOB。存储在其中的 json 具有以下结构：

{
moveid : "123",
movedate : "xyz",
submoves: [
{
submoveid: "1",
...
 },
{
submoveid : "2",
...
}
]
}

我知道我可以运行 Oracle 12c 查询来访问子移动列表：

select move.move_doc.submoves from move move

如何访问阵列的特定子移动？以及特定子移动中的属性？

Answer 1

你必须使用 Oracle 函数json_query和/或像这样json_value：

SELECT json_value(move_doc, '$.submoves[0].submoveid' RETURNING NUMBER) FROM move;

返回1 。

SELECT json_query(move_doc, '$.submoves[1]') FROM move;

将返回第二个 JSON 元素，即类似

{
submoveid : "2",
...
}

json_value用于检索标量值，json_query用于检索 JSON 值。您可能还想查看返回 SQL 结果表并因此可以在联接中使用的json_table。

有关更多示例，请参阅此 Oracle 文档

Answer 2

来自

Oracle JSON团队的Beda。

我们在版本 21c 中添加了新的多值索引，允许您索引 JSON 数组中的值。显然，21c 是全新的，您想知道如何在旧版本中执行此操作：功能索引（使用 JSON_Value 函数）仅限于每个 JSON 文档的单个值，因此无法索引数组值。但是：有一个"JSON 搜索索引"，它索引您的整个 JSON 文档，因此也索引数组中的值。另一种解决方案是使用实例化视图 usign JSON_Table。这会将数组值展开为单独的行。然后，您可以在该列上添加常规的 B 树索引。

示例代码如下：使用功能索引和 JSON 搜索索引的 JSON 索引https://livesql.oracle.com/apex/livesql/file/content_HN507PELCEEJGVNW4Q61L34DS.html

JSON 和具体化视图https://livesql.oracle.com/apex/livesql/file/content_HYMB1YBP4CPMG6T6MXY5G9X5L.html

Answer 3

根据我所看到的，在 Oracle 中，您可以将"整个数组"作为单个索引条目进行索引，但不能将数组的单个元素进行索引。

像MongoDB，Couchbase，Cassandra这样的NoSQL数据库具有"数组/集合"索引，可以索引数组中单个元素或对象的字段并查询它们。