Skip to Main Content
  • Questions
  • Parse JSON with JSON_TABLE, get array key and value

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Ulf.

Asked: September 12, 2020 - 10:34 pm UTC

Answered by: Chris Saxon - Last updated: September 14, 2020 - 3:15 pm UTC

Category: PL/SQL - Version: 20

You Asked

Hi,

I'm trying to parse JSON data from Eurostats where they use the JSON-Stat format which means a lot of key-value pairs.

Example: http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/tran_sf_roadse?precision=1&sex=F&time=2018&time=2017

I tried but couldn't get to query the values AND the key of the subarrays (e.g. the "value" array:

{
  "version": "2.0",
  "label": "Persons killed in road accidents by sex (CARE data)",
  "href": "http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/tran_sf_roadse?precision=1&sex=F&time=2018&time=2017",
  "source": "Eurostat",
  "updated": "2020-04-14",
  "status": {
    "30": ":",
    "31": ":",
    "36": ":",
    "37": ":",
    "47": ":",
    "49": ":",
    "51": ":",
    "58": ":",
    "59": ":"
  },
  "extension": {
    "datasetId": "tran_sf_roadse",
    "lang": "EN",
    "description": null,
    "subTitle": null,
    "status": {
      "label": {
        ":": "not available"
      }
    }
  },
  "class": "dataset",
  "value": {
    "0": 104,
    "1": 98,
    "2": 148,
    "3": 153,
    "4": 186,
    "5": 158,
    "6": 67,
    "7": 61,
    "8": 8,
    "9": 10,
    "10": 143,
    "11": 151,
    "12": 830,
    "13": 795,
    "14": 43,
    "15": 49,
    "16": 17,
    "17": 20,
    "18": 116,
    "19": 137,
    "20": 424,
    "21": 407,
    "22": 56,
    "23": 63,
    "24": 777,
    "25": 755,
    "26": 63,
    "27": 66,
    "28": 168,
    "29": 163,
    "32": 7,
    "33": 6,
    "34": 669,
    "35": 661,
    "38": 4,
    "39": 5,
    "40": 37,
    "41": 37,
    "42": 9,
    "43": 5,
    "44": 137,
    "45": 176,
    "46": 32,
    "48": 747,
    "50": 127,
    "52": 502,
    "53": 482,
    "54": 57,
    "55": 75,
    "56": 26,
    "57": 21,
    "60": 472,
    "61": 472
  },
  "dimension": {
    "unit": {
      "label": "unit",
      "category": {
        "index": {
          "NR": 0
        },
        "label": {
          "NR": "Number"
        }
      }
    },
    "sex": {
      "label": "sex",
      "category": {
        "index": {
          "F": 0
        },
        "label": {
          "F": "Females"
        }
      }
    },
    "geo": {
      "label": "geo",
      "category": {
        "index": {
          "AT": 0,
          "BE": 1,
          "BG": 2,
          "CH": 3,
          "CY": 4,
          "CZ": 5,
          "DE": 6,
          "DK": 7,
          "EE": 8,
          "EL": 9,
          "ES": 10,
          "FI": 11,
          "FR": 12,
          "HR": 13,
          "HU": 14,
          "IE": 15,
          "IS": 16,
          "IT": 17,
          "LT": 18,
          "LU": 19,
          "LV": 20,
          "MT": 21,
          "NL": 22,
          "NO": 23,
          "PL": 24,
          "PT": 25,
          "RO": 26,
          "SE": 27,
          "SI": 28,
          "SK": 29,
          "UK": 30
        },
        "label": {
          "AT": "Austria",
          "BE": "Belgium",
          "BG": "Bulgaria",
          "CH": "Switzerland",
          "CY": "Cyprus",
          "CZ": "Czechia",
          "DE": "Germany (until 1990 former territory of the FRG)",
          "DK": "Denmark",
          "EE": "Estonia",
          "EL": "Greece",
          "ES": "Spain",
          "FI": "Finland",
          "FR": "France",
          "HR": "Croatia",
          "HU": "Hungary",
          "IE": "Ireland",
          "IS": "Iceland",
          "IT": "Italy",
          "LT": "Lithuania",
          "LU": "Luxembourg",
          "LV": "Latvia",
          "MT": "Malta",
          "NL": "Netherlands",
          "NO": "Norway",
          "PL": "Poland",
          "PT": "Portugal",
          "RO": "Romania",
          "SE": "Sweden",
          "SI": "Slovenia",
          "SK": "Slovakia",
          "UK": "United Kingdom"
        }
      }
    },
    "time": {
      "label": "time",
      "category": {
        "index": {
          "2017": 0,
          "2018": 1
        },
        "label": {
          "2017": "2017",
          "2018": "2018"
        }
      }
    }
  },
  "id": [
    "unit",
    "sex",
    "geo",
    "time"
  ],
  "size": [
    1,
    1,
    31,
    2
  ]
}



Problem I'm facing, the keys are running number and can change according to the data. I loaded the JSON into a table (with the JSON check constraint) and tried it with the JSON_TABLE syntax:
--create table
CREATE TABLE TBL_EUROSTAT_RAW_JSON 
(
  ID NUMBER GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20 NOT NULL 
, IMPORTED_WHEN TIMESTAMP(6) DEFAULT SYSDATE 
, IMPORTED_JSON CLOB);
--add index
ALTER TABLE TBL_EUROSTAT_RAW_JSON
ADD CONSTRAINT TBL_EUROSTAT_RAW_JSON_CHK1 CHECK 
(imported_json is JSON)
ENABLE;

--load data
declare
  l_clob clob;
  l_buffer         varchar2(32767);
  l_amount         number;
  l_offset         number;
begin
 
  l_clob := apex_web_service.make_rest_request(
              p_url => 'https://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/tran_sf_roadse?precision=1&sex=F&time=2018&time=2017',
              p_http_method => 'GET'); 
    l_amount := 32000;
    l_offset := 1;
    begin
        loop
            dbms_lob.read( l_clob, l_amount, l_offset, l_buffer );
            dbms_output.put_line(l_buffer);
            l_offset := l_offset + l_amount;
            l_amount := 32000;
        end loop;

    exception
        when no_data_found then
            null;
    end;
 
      INSERT INTO tbl_eurostat_raw_json (
    imported_json
) VALUES (
    l_clob
);
 
end;
/

--QUERY
SELECT estat.*
  FROM tbl_eurostat_raw_json e,
       json_table(e.imported_json 
         COLUMNS (version PATH '$.version'
--,value     VARCHAR2(1000 CHAR) FORMAT JSON 
,   NESTED PATH '$.value.*' COLUMNS (
                    status_index  PATH '$.value'  ,
                    status_value PATH '$[*]'
                 )     
         )) AS estat;

and we said...

Thing is there are no arrays in that JSON. Just a big JSON objects.

I suggest creating a JSON search index with the Data Guide enabled. This gives you a couple of things:

- You can get the schema for a row or set of by calling JSON_Dataguide
- The ability to auto-create views with the JSON table expression

For example:

set define off 
insert into tbl_eurostat_raw_json (
 imported_json
) values (
  '{
  "version": "2.0",
  "label": "Persons killed in road accidents by sex (CARE data)",
  "href": "http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/tran_sf_roadse?precision=1&sex=F&time=2018&time=2017",
  "source": "Eurostat",
  "updated": "2020-04-14",
  "status": {
    "30": ":",
    "31": ":",
    "36": ":",
    "37": ":",
    "47": ":",
    "49": ":",
    "51": ":",
    "58": ":",
    "59": ":"
  },
  "extension": {
    "datasetId": "tran_sf_roadse",
    "lang": "EN",
    "description": null,
    "subTitle": null,
    "status": {
      "label": {
        ":": "not available"
      }
    }
  },
  "class": "dataset",
  "value": {
    "0": 104,
    "1": 98,
    "2": 148,
    "3": 153,
    "4": 186,
    "5": 158,
    "6": 67,
    "7": 61,
    "8": 8,
    "9": 10,
    "10": 143,
    "11": 151,
    "12": 830,
    "13": 795,
    "14": 43,
    "15": 49,
    "16": 17,
    "17": 20,
    "18": 116,
    "19": 137,
    "20": 424,
    "21": 407,
    "22": 56,
    "23": 63,
    "24": 777,
    "25": 755,
    "26": 63,
    "27": 66,
    "28": 168,
    "29": 163,
    "32": 7,
    "33": 6,
    "34": 669,
    "35": 661,
    "38": 4,
    "39": 5,
    "40": 37,
    "41": 37,
    "42": 9,
    "43": 5,
    "44": 137,
    "45": 176,
    "46": 32,
    "48": 747,
    "50": 127,
    "52": 502,
    "53": 482,
    "54": 57,
    "55": 75,
    "56": 26,
    "57": 21,
    "60": 472,
    "61": 472
  },
  "dimension": {
    "unit": {
      "label": "unit",
      "category": {
        "index": {
          "NR": 0
        },
        "label": {
          "NR": "Number"
        }
      }
    },
    "sex": {
      "label": "sex",
      "category": {
        "index": {
          "F": 0
        },
        "label": {
          "F": "Females"
        }
      }
    },
    "geo": {
      "label": "geo",
      "category": {
        "index": {
          "AT": 0,
          "BE": 1,
          "BG": 2,
          "CH": 3,
          "CY": 4,
          "CZ": 5,
          "DE": 6,
          "DK": 7,
          "EE": 8,
          "EL": 9,
          "ES": 10,
          "FI": 11,
          "FR": 12,
          "HR": 13,
          "HU": 14,
          "IE": 15,
          "IS": 16,
          "IT": 17,
          "LT": 18,
          "LU": 19,
          "LV": 20,
          "MT": 21,
          "NL": 22,
          "NO": 23,
          "PL": 24,
          "PT": 25,
          "RO": 26,
          "SE": 27,
          "SI": 28,
          "SK": 29,
          "UK": 30
        },
        "label": {
          "AT": "Austria",
          "BE": "Belgium",
          "BG": "Bulgaria",
          "CH": "Switzerland",
          "CY": "Cyprus",
          "CZ": "Czechia",
          "DE": "Germany (until 1990 former territory of the FRG)",
          "DK": "Denmark",
          "EE": "Estonia",
          "EL": "Greece",
          "ES": "Spain",
          "FI": "Finland",
          "FR": "France",
          "HR": "Croatia",
          "HU": "Hungary",
          "IE": "Ireland",
          "IS": "Iceland",
          "IT": "Italy",
          "LT": "Lithuania",
          "LU": "Luxembourg",
          "LV": "Latvia",
          "MT": "Malta",
          "NL": "Netherlands",
          "NO": "Norway",
          "PL": "Poland",
          "PT": "Portugal",
          "RO": "Romania",
          "SE": "Sweden",
          "SI": "Slovenia",
          "SK": "Slovakia",
          "UK": "United Kingdom"
        }
      }
    },
    "time": {
      "label": "time",
      "category": {
        "index": {
          "2017": 0,
          "2018": 1
        },
        "label": {
          "2017": "2017",
          "2018": "2018"
        }
      }
    }
  },
  "id": [
    "unit",
    "sex",
    "geo",
    "time"
  ],
  "size": [
    1,
    1,
    31,
    2
  ]
}'
);

commit;

create search index euro_json_i 
  on tbl_eurostat_raw_json ( imported_json )
  for json
  parameters ( 'dataguide on' );
  
set long 1000
select json_dataguide ( imported_json, dbms_json.format_hierarchical, dbms_json.pretty )
from   tbl_eurostat_raw_json;

JSON_DATAGUIDE(IMPORTED_JSON,DBMS_JSON.FORMAT_HIERARCHICAL,DBMS_JSON.PRETTY)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
{
  "type" : "object",
  "properties" :
  {
    "id" :
    {
      "type" : "array",
      "o:length" : 32,
      "o:preferred_column_name" : "id",
      "items" :
      {
        "type" : "string",
        "o:length" : 4,
        "o:preferred_column_name" : "scalar_string"
      }
    },
    "href" :
    {
      "type" : "string",
      "o:length" : 128,
      "o:preferred_column_name" : "href"
    },
    "size" :
    {
      "type" : "array",
      "o:length" : 16,
      "o:preferred_column_name" : "size",
      "items" :
      {
        "type" : "number",
        "o:length" : 2,
        "o:preferred_column_name" : "scalar_number"
      }
    }
    ...

insert into tbl_eurostat_raw_json (
 imported_json
) values ( '{ "other": "value" }' );
commit;

select json_dataguide ( imported_json, dbms_json.format_hierarchical, dbms_json.pretty ) 
from   tbl_eurostat_raw_json
where  id = 2;

JSON_DATAGUIDE(IMPORTED_JSON,DBMS_JSON.FORMAT_HIERARCHICAL,DBMS_JSON.PRETTY)                                                                                           
{
  "type" : "object",
  "properties" :
  {
    "other" :
    {
      "type" : "string",
      "o:length" : 8,
      "o:preferred_column_name" : "other"
    }
  }
} 

begin 
  dbms_json.create_view_on_path ( 
    'euro_view_values', 
    'tbl_eurostat_raw_json', 
    'imported_json', 
    '$.value' 
  );
end;
/

select id, "IMPORTED_JSON$class", "IMPORTED_JSON$other", 
       "IMPORTED_JSON$0", 
       "IMPORTED_JSON$1", 
       "IMPORTED_JSON$2" --etc.
from   euro_view_values;

ID    IMPORTED_JSON$class    IMPORTED_JSON$other     IMPORTED_JSON$0    IMPORTED_JSON$1    IMPORTED_JSON$2   
    1 dataset                <null>                                104                 98                148 
    2 <null>                 value                              <null>             <null>             <null> 


Calling create_view_on_path re-creates the view, using the attributes that are currently in the table. Passing the path allows you to state which nested objects to return.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.