如何从Oracle JSON列获取数组索引？(How to get array index from Oracle JSON column?)

如何从Oracle JSON列获取数组索引？(How to get array index from Oracle JSON column?)

有这样的JSON（我知道JSON不支持注释，在这种情况下用于说明这个想法）：

{ "people": [ { --// <-- index 0 "id": 100, "name": "John Doe" }, { --// <-- index 1 "id": 101, "name": "Jane Roe" } ] }

我们可以从数组中的特定元素中选择值，如下所示：

SELECT name FROM JSON_TABLE( '{ "people": [ { "id": 100, "name": "John Doe" }, { "id": 101, "name": "Jane Roe" }, ] }', '$.people[*]' COLUMNS( ID NUMBER PATH '$.id', NAME VARCHAR2 PATH '$.name' ) ) info WHERE info.id = 101

结果：

NAME -------- Jane Roe

有没有办法获得数组中的元素索引？ 就像是：

SELECT array_index --// <-- how get the array index of the element found? FROM JSON_TABLE( --// ... ) info WHERE info.id = 101

结果：

ARRAY_INDEX ----------- 1

有可能在Oracle 12c中使用JSON支持来做类似的事情吗？

Having a JSON like this (I know that JSON doesn't support comments. Used in this case to illustrate the idea):

{ "people": [ { --// <-- index 0 "id": 100, "name": "John Doe" }, { --// <-- index 1 "id": 101, "name": "Jane Roe" } ] }

We can select values from specific elements in the array doing something like this:

SELECT name FROM JSON_TABLE( '{ "people": [ { "id": 100, "name": "John Doe" }, { "id": 101, "name": "Jane Roe" }, ] }', '$.people[*]' COLUMNS( ID NUMBER PATH '$.id', NAME VARCHAR2 PATH '$.name' ) ) info WHERE info.id = 101

Result:

NAME -------- Jane Roe

Is there a way to get the element index in the array? Something like:

SELECT array_index --// <-- how get the array index of the element found? FROM JSON_TABLE( --// ... ) info WHERE info.id = 101

Result:

ARRAY_INDEX ----------- 1

Is possible to do something like this using JSON support in Oracle 12c?

最满意答案

COLUMNS( idx FOR ORDINALITY, ID NUMBER PATH '$.id', NAME VARCHAR2 PATH '$.name' )

应该为你工作

COLUMNS( idx FOR ORDINALITY, ID NUMBER PATH '$.id', NAME VARCHAR2 PATH '$.name' )

should work for you