I have a table "move" with one column "move_doc" which is a CLOB. The json stored inside has the structure:
{
moveid : "123",
movedate : "xyz",
submoves: [
{
submoveid: "1",
...
},
{
submoveid : "2",
...
}
]
}
I know I can run an Oracle 12c query to access the submoves list with:
select move.move_doc.submoves from move move
How do I access particular submoves of the array? And the attributes inside a particular submove?
You have to use Oracle functions json_query and/or json_value like this:
SELECT json_value(move_doc, '$.submoves[0].submoveid' RETURNING NUMBER) FROM move;
returns 1.
SELECT json_query(move_doc, '$.submoves[1]') FROM move;
would return the second JSON element, i.e. something like
{
submoveid : "2",
...
}
json_value is used to retrieve a scalar value, json_query is used to retrieve JSON values. You might also want to have a look at json_table which returns an SQL result table and thus can be used in Joins.
See this Oracle Doc for more examples
Beda here from the Oracle JSON team.
We have added a new multi-value index in release 21c allowing you to index values from a JSON array. Obviously, 21c is brand new and you want to know how to do this in older releases: Functional indexes (using JSON_Value function) are limited to a single value per JSON document and therefore are not capable to index array values. But: there is a 'JSON search index' which indexes your entire JSON document and therefore also values in the array. Another solution is to use a materialized view usign JSON_Table. This will expand the array values into separate rows.Then you can add a regular B-Tree index on that column.
Sample code here: JSON indexing with functional indexes and JSON search index https://livesql.oracle.com/apex/livesql/file/content_HN507PELCEEJGVNW4Q61L34DS.html
JSON and materialized views https://livesql.oracle.com/apex/livesql/file/content_HYMB1YBP4CPMG6T6MXY5G9X5L.html
From what I've looked, In Oracle, you can index the "whole array" as a single index entry, but not individual elements of an array.
NoSQL databases like MongoDB, Couchbase, Cassandra have "array/collection" indexes which can index individual elements or fields of objects within an array and query them.
