How to read a json with oracle?
1 answer
by
orpha ,
a year ago
@deron
To read a JSON file in Oracle, you can use the JSON_TABLE function or the APEX_JSON package. Here is an example of how you can read a JSON file using the JSON_TABLE function:
- Create a table to store the JSON data:
1 2 3 |
CREATE TABLE json_data ( json CLOB ); |
- Insert the JSON data into the table:
1
|
INSERT INTO json_data VALUES ('{"name": "John", "age": 30}');
|
- Use the JSON_TABLE function to extract data from the JSON file:
1 2 3 4 5 6 7 8 |
SELECT *
FROM json_data
CROSS APPLY JSON_TABLE(json, '$'
COLUMNS (
name VARCHAR2(50) PATH '$.name',
age NUMBER PATH '$.age'
)
) jt;
|
This will output the data from the JSON file in tabular format.
Alternatively, you can use the APEX_JSON package to read a JSON file in Oracle. Here is an example of how you can do this:
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE
l_json CLOB;
BEGIN
l_json := '{"name": "John", "age": 30}';
APEX_JSON.PARSE(l_json);
DBMS_OUTPUT.PUT_LINE('Name: ' || APEX_JSON.GET_VARCHAR2(p_path => 'name'));
DBMS_OUTPUT.PUT_LINE('Age: ' || APEX_JSON.GET_NUMBER(p_path => 'age'));
APEX_JSON.FREE;
END;
|
This will output the data from the JSON file using the APEX_JSON package.