如何从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 = 101Result:
NAME -------- Jane RoeIs 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 = 101Result:
ARRAY_INDEX ----------- 1Is 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
更多推荐
发布评论