How To Parse JSON DATA IN ORACLE-CodePudding

A table has JSON data in the HUGECLOB column. And I want to parse it. How can I do it?

{"errors":{"destination_country_id":["can not be blank"],"dispatch_country_id":["can not be blank"],"vehicle_id":["can not be blank"],"trailer_id":["can not be blank"]}}

I tried this;

  SELECT t.*
    FROM table,
         JSON_TABLE(_hugeclob_data, '$'
              COLUMNS (destination_country_id  VARCHAR2(50 CHAR) PATH '$.destination_country_id',
                       dispatch_country_id     VARCHAR2(50 CHAR) PATH '$.dispatch_country_id',
                       vehicle_id              VARCHAR2(50 CHAR) PATH '$.vehicle_id',
                       trailer_id              VARCHAR2(50 CHAR) PATH '$.trailer_id'                  
                      )
                  ) t;

CodePudding user response：

If the fields are scalars but defined as arrays (like in your sample), do it like this

SELECT t.*
    FROM table t1,
         JSON_TABLE(_hugeclob_data format json, '$.errors'
              COLUMNS (destination_country_id  VARCHAR2(50 CHAR) PATH '$.destination_country_id[*]',
                       dispatch_country_id     VARCHAR2(50 CHAR) PATH '$.dispatch_country_id[*]',
                       vehicle_id              VARCHAR2(50 CHAR) PATH '$.vehicle_id[*]',
                       trailer_id              VARCHAR2(50 CHAR) PATH '$.trailer_id[*]'                  
                      )
                  ) t;

But if they are actually arrays, you'll need to use 'nested path' declaration to unwind the arrays

CodePudding user response：

Here is one option. It takes the first element of the each of the arrays:

create table test_table (
    id                             number generated by default on null as identity 
                                   constraint test_table_id_pk primary key,
    clob_data                      clob  CHECK (clob_data IS JSON)
)
;

INSERT INTO test_table(clob_data) VALUES
(
'{
  "errors": {
    "destination_country_id": [
      "can not be blank"
    ],
    "dispatch_country_id": [
      "can not be blank"
    ],
    "vehicle_id": [
      "can not be blank"
    ],
    "trailer_id": [
      "can not be blank"
    ]
  }
}');

SELECT t.*
FROM test_table,
JSON_TABLE(clob_data, '$.errors'
COLUMNS (row_number FOR ORDINALITY,
         destination_country_id VARCHAR2(4000) PATH '$.destination_country_id[0]',
         dispatch_country_id VARCHAR2(4000) PATH '$.dispatch_country_id[0]',
         vehicle_id VARCHAR2(4000) PATH '$.vehicle_id[0]',
         trailer_id VARCHAR2(4000) PATH '$.trailer_id[0]'
        ))
AS t;

ROW_NUMBER DESTINATION_COUNTRY_ DISPATCH_COUNTRY_ID  VEHICLE_ID           TRAILER_ID          
---------- -------------------- -------------------- -------------------- --------------------
         1 can not be blank     can not be blank     can not be blank     can not be blank