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;
2
Answers
If the fields are scalars but defined as arrays (like in your sample), do it like this
But if they are actually arrays, you’ll need to use ‘nested path’ declaration to unwind the arrays
Here is one option. It takes the first element of the each of the arrays: