Oracle 在JSON查询中获取节点密钥

CREATE TABLE j_computerlist
  (computer_data CLOB CONSTRAINT ensure_json CHECK (computer_data IS JSON));

CREATE TABLE Mytable (
Computers varchar2(200),
Idnumber NUMBER(10),
Environment varchar2(200),
LastReportTime date,
firstcheckin date,
status_code varchar2(100)
);  
  
  INSERT INTO j_computerlist
  VALUES ('{
    "Computers": {
        "quebrada": [
            {
                "id": "927003",
                "environment": "iem-cis",
                "lastReportTime": "Tue, 27 Feb 2018 08:31:46 -0600",
                "ipAddress": "10.205.2.68,10.220.91.116",
                "locked": "False",
                "firstCheckin": "Wed, 24 Jun 2015 09:14:57 -0600",
                "lastUDPping": "Tue, 27 Feb 2018 08:00:27 -0600"
            }
        ],
        "quitaque": [
            {
                "id": "2220618",
                "environment": "iem-cis",
                "lastReportTime": "Tue, 27 Feb 2018 08:33:07 -0600",
                "ipAddress": "10.204.103.53",
                "locked": "False",
                "firstCheckin": "Fri, 14 Nov 2014 03:08:54 -0600",
                "lastUDPping": "Tue, 27 Feb 2018 07:59:53 -0600"
            }
        ],
        "quenemo": [
            {
                "id": "4091629",
                "environment": "iem-cis",
                "lastReportTime": "Tue, 27 Feb 2018 08:29:00 -0600",
                "ipAddress": "10.205.2.69,10.220.91.117",
                "locked": "False",
                "firstCheckin": "Wed, 24 Jun 2015 09:14:57 -0600",
                "lastUDPping": "Tue, 27 Feb 2018 08:00:19 -0600"
            }
        ]
    },
    "status_text": "",
    "error_text": "",
    "status_code": "200",
    "status": "OK",
    "rc": "0"
}');

commit;

insert into mytable 
????

如何使用JSON查询获取密钥并以适当的格式转换日期数据，以便我可以在mytable中插入数据，如下所示

select * from mytable ;

computers    idnumber    environment LastReportTime  firstcheckin    status_code

quebrada    927003  iem-cis 27 Feb 2018 08:31:46    24 Jun 2015 03:14:57    200
quitaque    2220618 iem-cis 27 Feb 2018 08:33:07    13 Nov 2014 21:08:54    200
quenemo     4091629 iem-cis 27 Feb 2018 08:29:00    24 Jun 2015 03:14:57    200

您可以使用JSON_table将文档转换为关系结构。

但是!

这将提取值。文档中的计算机 (quebrada等) 是attributes。

您可以使用嵌套路径 (wildcarding属性) 轻松拉出其他值:

select j。*
from   j_computerlist, json_table (
  computer_data, '$'
  columns (
    computers varchar2(10) path '$。computers',
    nested path '$。Computers。*'
    columns (
      pos            for ordinality,
      idnumber       varchar2(10) path '$。id',  
      environment    varchar2(10) path '$。environment',
      LastReportTime varchar2(40) path '$。lastReportTime',
      firstcheckin   varchar2(40) path '$。firstCheckin'
    ),
    status_code     varchar2(10) path '$。status_code'
  )
) j;

COMPUTERS   STATUS_CODE   POS   IDNUMBER   ENVIRONMENT   LASTREPORTTIME                    FIRSTCHECKIN                      
      200               1 927003     iem-cis       Tue, 27 Feb 2018 08:31:46 -0600   Wed, 24 Jun 2015 09:14:57 -0600   
      200               2 2220618    iem-cis       Tue, 27 Feb 2018 08:33:07 -0600   Fri, 14 Nov 2014 03:08:54 -0600   
      200               3 4091629    iem-cis       Tue, 27 Feb 2018 08:29:00 -0600   Wed, 24 Jun 2015 09:14:57 -0600

"For ordinality" returns the position within the document。 So if the computers are fixed you could use this to generate the names。 But if you need to pull the computers out dynamically, it's tougher。

In PL/SQL you can use the get_keys method of json_object_t to extract attribute names。 Sadly this doesn't work in SQL。

So you could build a function that accepts the document and the attribute position you want to extract。 Then return the name of this:

create or replace function get_key ( pos int, json varchar2 ) 
  return varchar2 as
  jdoc     json_object_t;
  doc_keys json_key_list;
begin

  jdoc := json_object_t。parse ( json );
  
  doc_keys := treat ( jdoc。get('Computers') as json_object_t)。get_keys;
  
  return doc_keys(pos);
end get_key;
/

然后，您可以将序数列传递给这个:

select get_key(pos, computer_data) computer, j。*
from   j_computerlist, json_table (
  computer_data
  columns (
    nested path '$。Computers。*'
    columns (
      pos for ordinality,
      id, environment, lastReportTime, firstCheckin
    ),
    status_code
  )
) j;

COMPUTER   POS   ID        ENVIRONMENT   LASTREPORTTIME                    FIRSTCHECKIN                      STATUS_CODE   
quebrada       1 927003    iem-cis       Tue, 27 Feb 2018 08:31:46 -0600   Wed, 24 Jun 2015 09:14:57 -0600   200           
quitaque       2 2220618   iem-cis       Tue, 27 Feb 2018 08:33:07 -0600   Fri, 14 Nov 2014 03:08:54 -0600   200           
quenemo        3 4091629   iem-cis       Tue, 27 Feb 2018 08:29:00 -0600   Wed, 24 Jun 2015 09:14:57 -0600   200 

If you have control of the JSON document, it would be better to change it so computer (names?) are values instead though。 e。g。:

"name": "quenemo",
  "data": [
            {
                "id": "4091629",
                "environment": "iem-cis",
                "lastReportTime": "Tue, 27 Feb 2018 08:29:00 -0600",
                "ipAddress": "10。205。2。69,10。220。91。117",
                "locked": "False",
                "firstCheckin": "Wed, 24 Jun 2015 09:14:57 -0600",
                "lastUDPping": "Tue, 27 Feb 2018 08:00:19 -0600"
            }
        ]

This enables you to get all values with JSON_table。 And avoid building the custom function!

Note in the last example above I've used 18c's simplified json_table syntax。 This infers paths from the names:

https://livesql。oracle。com/apex/livesql/file/content_F81S3YX84C0VNUMFDJLFR3F1E。html