I'm trying to get some specific dataset from Scival REST API to oracle database table. Below is the JSON payload that I'm trying to manipulate.

{
    "metrics": [{
        "metricType": "ScholarlyOutput",
        "valueByYear": {
            "2017": 4,
            "2018": 0,
            "2019": 3,
            "2020": 1,
            "2021": 1
        }
    }],
    "author": {
        "link": {
            "@ref": "self",
            "@href": "https://api.elsevier.com/analytics/scival/author/123456789?apiKey=xxxxxxxxxx&httpAccept=text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2",
            "@type": "text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2"
        },
        "name": "Citizen, John",
        "id": 123456789,
        "uri": "Author/123456789"
    }
}

I'm able to query the 'author' bit with the below SQL.

SELECT jt.*
FROM TABLE d,
     JSON_TABLE(d.column format json, '$.author' COLUMNS (
       "id" VARCHAR2 PATH '$.id',
       "name" VARCHAR2 PATH '$.name')
      ) jt;

However, I'm not able to get the 'valueByYear' value. I've tried below.

SELECT jt.*
FROM TABLE d,
     JSON_TABLE 
            (d.column, '$.metrics[*]' COLUMNS 
                (
                "metric_Type" VARCHAR2 PATH '$.metricType'
                ,"Value_By_Year" NUMBER PATH '$.valueByYear'
                  NESTED PATH '$.valueByYear[1]' COLUMNS 
                   ("2021" NUMBER PATH '$.valueByYear[1]'
                   )
                )
            ) jt;

I would appreciate if you could let me know what I'm missing here. I'm after the latest 'year' value.

CodePudding user response：

You can use:

SELECT jt.*
FROM   table_name d,
       JSON_TABLE(
         d.column_name format json,
         '$'
         COLUMNS (
           id   VARCHAR2 PATH '$.author.id',
           name VARCHAR2 PATH '$.author.name',
           NESTED PATH '$.metrics[*]' COLUMNS (
             metricType VARCHAR2(30) PATH '$.metricType',
             value2021  NUMBER       PATH '$.valueByYear."2021"'
           )
         )
       ) jt;

Which, for the sample data:

CREATE TABLE table_name (
  column_name CLOB CHECK (column_name IS JSON)
);

INSERT INTO table_name (column_name) VALUES (
'{
    "metrics": [{
        "metricType": "ScholarlyOutput",
        "valueByYear": {
            "2017": 4,
            "2018": 0,
            "2019": 3,
            "2020": 1,
            "2021": 1
        }
    }],
    "author": {
        "link": {
            "@ref": "self",
            "@href": "https://api.elsevier.com/analytics/scival/author/123456789?apiKey=xxxxxxxxxx&httpAccept=text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2",
            "@type": "text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2"
        },
        "name": "Citizen, John",
        "id": 123456789,
        "uri": "Author/123456789"
    }
}'
);

Outputs:

ID	NAME	METRICTYPE	VALUE2021
123456789	Citizen, John	ScholarlyOutput	1

db<>fiddle here

CodePudding user response：

If you want to do it dynamically in PL/SQL, then you can create the types:

CREATE TYPE scival_row IS OBJECT(
  name       VARCHAR2(100),
  id         NUMBER(12),
  metricType VARCHAR2(50),
  year       NUMBER(4),
  value      NUMBER
);

CREATE TYPE scival_tbl IS TABLE OF scival_row;

and then the pipelined function:

CREATE FUNCTION parseScival(
  i_json CLOB,
  i_year NUMBER
) RETURN scival_tbl PIPELINED DETERMINISTIC
IS
  v_obj     JSON_OBJECT_T := JSON_OBJECT_T.parse(i_json);
  v_author  JSON_OBJECT_T := v_obj.get_Object('author');
  v_name    VARCHAR2(100) := v_author.get_String('name');
  v_id      NUMBER(12)    := v_author.get_Number('id');
  v_metrics JSON_ARRAY_T  := v_obj.get_Array('metrics');
  v_metric  JSON_OBJECT_T;
BEGIN
  FOR i IN 0 .. v_metrics.Get_Size - 1 LOOP
    v_metric := TREAT(v_metrics.get(i) AS JSON_OBJECT_T);
    PIPE ROW(
      scival_row(
        v_name,
        v_id,
        v_metric.get_string('metricType'),
        i_year,
        v_metric.get_object('valueByYear').get_number(i_year)
      )
    );
  END LOOP;
END;
/

Then you can use the query:

SELECT j.*
FROM   table_name t
       CROSS APPLY TABLE(parseScival(t.column_name, 2021)) j

Which outputs:

NAME	ID	METRICTYPE	YEAR	VALUE
Citizen, John	123456789	ScholarlyOutput	2021	1

db<>fiddle here