Oracle 使用任意键访问Json字符串

嗨

我正在通过RESTful服务以以下格式接收JSON字符串:

{
 "data": {
  "244376115423591": {
   "first_name": "John",
   "last_name": "Doe",
   "review_date": "",
   "startDate": "01-08-2019",
   "endDate": "",
   "feee": 0,
   "feeeWkHours": "0",
   "stretch": 0,
   "payment": "300",
   "recalc": "n"
  },
                "1234567890": {
   "first_name": "Jane",
   "last_name": "Doe",
   "review_date": "",
   "startDate": "01-09-2019",
   "endDate": "",
   "feee": 0,
   "feeeWkHours": "0",
   "stretch": 0,
   "payment": "250",
   "recalc": "n"
  }
 },
 "action": "edit"
}

我不确定在SQL中如何访问元素 “first_name”。元素 “244376115423591” 和 “1234567890” 是完全随机的并且可以是任何数值。

这应该会让你开始

SQL> create table t (
  2    j varchar2(4000),
  3    constraint chk check ( j is json ));

Table created.

SQL>
SQL>
SQL> insert into t values ('{
  2   "data": {
  3    "244376115423591": {
  4     "first_name": "John",
  5     "last_name": "Doe",
  6     "review_date": "",
  7     "startDate": "01-08-2019",
  8     "endDate": "",
  9     "feee": 0,
 10     "feeeWkHours": "0",
 11     "stretch": 0,
 12     "payment": "300",
 13     "recalc": "n"
 14    },
 15    "1234567890": {
 16     "first_name": "Jane",
 17     "last_name": "Doe",
 18     "review_date": "",
 19     "startDate": "01-09-2019",
 20     "endDate": "",
 21     "feee": 0,
 22     "feeeWkHours": "0",
 23     "stretch": 0,
 24     "payment": "250",
 25     "recalc": "n"
 26    }
 27   },
 28   "action": "edit"
 29  }  ');

1 row created.

SQL>
SQL>
SQL> select d.*
  2  from t,
  3       json_table(
  4           t.j,
  5           '$.data.*'
  6               columns (
  7                 first_name      varchar2(30 char)    path '$.first_name',
  8                 last_name      varchar2(30 char)    path '$.last_name'
  9                )
 10       ) d;

FIRST_NAME           LAST_NAME
-------------------- --------------------
John                 Doe
Jane                 Doe