如何从Oracle JSON列中获取数组索引？

先看这个演示用的JSON结构（注：实际JSON不支持注释，这里仅为标注数组索引）：

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

这个JSON里包含一个people数组，数组里每个元素都是带id和name字段的人员对象。如果要从这个数组里提取所有人员的姓名，可以用JSON_TABLE来实现，完整的SQL查询语句如下：

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

这里几个关键部分要划重点：

• '$.people[*]'：这个JSON路径表示遍历people数组下的所有元素，相当于把数组拆成一行行的独立记录
• COLUMNS子句：用来定义SQL结果集中的列，每个列对应JSON里的一个字段：
  • ID NUMBER PATH '$.id'：把每个数组元素里的id字段提取出来，转换成SQL的NUMBER类型，命名为ID列
  • NAME VARCHAR2(50) PATH '$.name'：同理提取name字段，转换成VARCHAR2类型，命名为NAME列

执行这个查询后，就能得到如下结果：

John Doe
Jane Roe

内容的提问来源于stack exchange，提问作者Juan Carlos Mendoza