Oracle SQL: Parse JSON in PL/SQL to table or array
From Oracle 12, you can do all the parsing in an SQL query. Your main issue is not the JSON but that you have your data in strings and you will need to split those into lines and then into value and correlate the values with the headers:
SELECT p.*
FROM (
SELECT l.lineno,
kv.key,
kv.value
FROM table_name t
CROSS APPLY JSON_TABLE(
t.value,
'$.result.optimizationData[*]?(@.name == "unit_out")'
COLUMNS
content CLOB PATH '$.content'
) j
CROSS JOIN LATERAL (
SELECT LEVEL AS lineno,
REGEXP_SUBSTR(j.content, '.+', 1, 1 ) AS header,
REGEXP_SUBSTR(j.content, '.+', 1, LEVEL ) AS line
FROM DUAL
WHERE LEVEL > 1
CONNECT BY LEVEL <= REGEXP_COUNT(j.content, '.+')
) l
CROSS JOIN LATERAL (
SELECT CAST(REGEXP_SUBSTR(header, '[^;]+', 1, LEVEL) AS VARCHAR2(4000))
AS key,
CAST(REGEXP_SUBSTR(line, '[^;]+', 1, LEVEL) AS VARCHAR2(4000))
AS value
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(header, '[^;]+')
) kv
) pt
PIVOT (
MAX(value)
FOR key IN (
'ID_LIMIT' AS id_limit,
'CODE_UNIT' AS code_limit,
'TIME_STAMP_FROM' AS time_stamp_from,
'TIME_STAMP_TO' AS time_stamp_to,
'VARIABLE' AS variable,
'VALUE' AS value
)
) p
Which, for the sample data:
CREATE TABLE table_name (value BLOB CHECK (value IS JSON));
INSERT INTO table_name (value) VALUES (
'{
"statusCode": 200,
"isValid": true,
"errors": [],
"result": {
"optimizationData": [
{
"name": "out",
"content": "ID_LIMIT;TIME_STAMP_FROM;DIRECTION;ID_MODEL_CONSTRAINT\n1;202109222200;G;2_7_1_G\n1;202109232200;G;2_3_1_G\n2;202109222200;G;2_3_1_G\n3;202109222200;G;3_3_1_P\n"
},
{
"name": "unit_out",
"content": "ID_LIMIT;CODE_UNIT;TIME_STAMP_FROM;TIME_STAMP_TO;VARIABLE;VALUE\n1;BEL 2-02;202109222200;202109232200;RelaxationPlus;10\n1;BEL 2-05;202109222200;202109232200;RelaxationPlus;10\n2;WLO 1-01;202109222200;202109232200;RelaxationMinus;10\n"
}
]
}
}'
);
Outputs:
LINENO ID_LIMIT CODE_LIMIT TIME_STAMP_FROM TIME_STAMP_TO VARIABLE VALUE 2 1 BEL 2-02 202109222200 202109232200 RelaxationPlus 10 3 1 BEL 2-05 202109222200 202109232200 RelaxationPlus 10 4 2 WLO 1-01 202109222200 202109232200 RelaxationMinus 10
Parse JSON into Oracle table using PL/SQL
I used PL/JSON library. Specifically, JSON_EXT package functions in order to parse it.
The following script inspired by Oracle Community answer worked for me
DECLARE
l_param_list VARCHAR2(512);
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_response_text VARCHAR2(32767);
l_list json_list;
A_id VARCHAR2(200);
UserId VARCHAR2(100);
UserName VARCHAR2(100);
OutletCode VARCHAR2(100);
OutletName VARCHAR2(100);
MobileNumber VARCHAR2(100);
PhoneNumber VARCHAR2(100);
Address VARCHAR2(100);
City VARCHAR2(100);
State VARCHAR2(100);
Postcode VARCHAR2(100);
Email VARCHAR2(100);
UpdateCount VARCHAR2(100);
loginCount VARCHAR2(100);
ReferencePhoto VARCHAR2(100);
Updates VARCHAR2(100);
AccountLocked VARCHAR2(100);
Oracle_Flag VARCHAR2(100);
acl VARCHAR2(100);
BEGIN
-- service's input parameters
-- preparing Request...
l_http_request := UTL_HTTP.begin_request('https://api.appery.io/rest/1/db/collections/Outlet_Details?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D'
, 'GET'
, 'HTTP/1.1');
-- ...set header's attributes
UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');
--UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));
-- ...set input parameters
-- UTL_HTTP.write_text(l_http_request, l_param_list);
-- get Response and obtain received value
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.read_text(l_http_response, l_response_text);
DBMS_OUTPUT.put_line(l_response_text);
l_list := json_list(l_response_text);
FOR i IN 1..l_list.count
LOOP
A_id := json_ext.get_string(json(l_list.get(i)),'_id');
UserId := json_ext.get_string(json(l_list.get(i)),'UserId');
UserName := json_ext.get_string(json(l_list.get(i)),'UserName');
OutletCode := json_ext.get_string(json(l_list.get(i)),'OutletCode');
OutletName := json_ext.get_string(json(l_list.get(i)),'OutletName');
MobileNumber := json_ext.get_string(json(l_list.get(i)),'MobileNumber');
PhoneNumber := json_ext.get_string(json(l_list.get(i)),'PhoneNumber');
Address := json_ext.get_string(json(l_list.get(i)),'Address');
City := json_ext.get_string(json(l_list.get(i)),'City');
State := json_ext.get_string(json(l_list.get(i)),'State');
Postcode := json_ext.get_string(json(l_list.get(i)),'Postcode');
Email := json_ext.get_string(json(l_list.get(i)),'Email');
UpdateCount := json_ext.get_string(json(l_list.get(i)),'UpdateCount');
loginCount := json_ext.get_string(json(l_list.get(i)),'loginCount');
ReferencePhoto := json_ext.get_string(json(l_list.get(i)),'ReferencePhoto');
Updates := json_ext.get_string(json(l_list.get(i)),'Updates');
AccountLocked := json_ext.get_string(json(l_list.get(i)),'AccountLocked');
Oracle_Flag := json_ext.get_string(json(l_list.get(i)),'Oracle_Flag');
acl := json_ext.get_string(json(l_list.get(i)),'acl');
insert .....Notice that json_ext.get_string retuns only VARCHAR2 limited to 32767 max. In order to use the same package with larger json_list and json_values (>32KB) check here.
If you have APEX 5.0 and above, better option and much better performance via APEX_JSON package. See @Olafur Tryggvason's answer for details
How can I parse JSON string in PL/SQL
Oracle 12c supports JSON
if you have an existing table simply do
ALTER TABLE table1 ADD CONSTRAINT constraint_name CHECK (your_column IS json);
SELECT t.your_column.id FROM table1 t;Note that for some reason
tnickname is necessary thereOr complete example:
CREATE TABLE json_documents (
id RAW(16) NOT NULL,
data CLOB,
CONSTRAINT json_documents_pk PRIMARY KEY (id),
CONSTRAINT json_documents_json_chk CHECK (data IS JSON)
);
INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(),
'{
"FirstName" : "John",
"LastName" : "Doe",
"Job" : "Clerk",
"Address" : {
"Street" : "99 My Street",
"City" : "My City",
"Country" : "UK",
"Postcode" : "A12 34B"
},
"ContactDetails" : {
"Email" : "john.doe@example.com",
"Phone" : "44 123 123456",
"Twitter" : "@johndoe"
},
"DateOfBirth" : "01-JAN-1980",
"Active" : true
}');
SELECT a.data.FirstName,
a.data.LastName,
a.data.Address.Postcode AS Postcode,
a.data.ContactDetails.Email AS Email
FROM json_documents a;
FIRSTNAME LASTNAME POSTCODE EMAIL
--------------- --------------- ---------- -------------------------
Jayne Doe A12 34B jayne.doe@example.com
John Doe A12 34B john.doe@example.com
2 rows selected.More info
https://oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1
https://docs.oracle.com/database/122/ADJSN/using-PLSQL-object-types-for-JSON.htm#ADJSN-GUID-F0561593-D0B9-44EA-9C8C-ACB6AA9474EE
Parse Json and insert into oracle table
$.dataselects the array, but the array doesn't have a.pidkey, for example.$.data[*]selects all the items in the array, which is what you want.SELECT key1, key2 , key3,key4,key5
FROM JSON_TABLE ('
{
"data": [{
"pid": "10",
"name": "sss",
"consumer_price": "100",
"discount": "10",
"sale_price": "90"
}, {
"pid": "11",
"name": "fff",
"consumer_price": "100",
"discount": "10",
"sale_price": "90"
}]
}
',
'$.data[*]'
COLUMNS
key1 VARCHAR2 PATH '$.pid',
key2 VARCHAR2 PATH '$.name',
key3 VARCHAR2 PATH '$.consumer_price',
key4 VARCHAR2 PATH '$.discount',
key5 VARCHAR2 PATH '$.sale_price'
);ORACLE: The best way to parse JSON into collection of object types
If you're on 19c or higher, the best way is to state your object as the return type for a
JSON_valuecall and the database will map it for you:create type t_ref_rec is object(id number, name varchar2(256));
/
create type t_ref_tbl is table of t_ref_rec;
/
select json_value (
'[{"id":1, "name":"one"}, {"id":2, "name":"two"}]',
'$' returning t_ref_tbl
) obj
from dual;
OBJ(ID, NAME)
---------------------------------------------------
T_REF_TBL(T_REF_REC(1, 'one'), T_REF_REC(2, 'two'))The
on mismatchclause enable you to define what happens if there's a difference between the JSON document and the object type.Parse json through json_table in oracle 18
You can define the functions:
CREATE FUNCTION get_keys(
value IN CLOB
) RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
js JSON_OBJECT_T := JSON_OBJECT_T( value );
keys JSON_KEY_LIST;
BEGIN
keys := js.get_keys();
FOR i in 1 .. keys.COUNT LOOP
PIPE ROW ( keys(i) );
END LOOP;
END;
/
CREATE FUNCTION get_value(
value IN CLOB,
path IN VARCHAR2
) RETURN VARCHAR2
IS
js JSON_OBJECT_T := JSON_OBJECT_T( value );
BEGIN
RETURN js.get_string( path );
END;
/Then use the query:
WITH j (sJson) as (
select '{
"ID":"1444284517",
"ID_ORD":"4255;2187606199",
"Vals":{
"CODE":"ONB2B3BB8",
"DORD":"25.04.2021"
}
}'
from dual
)
SELECT jt.id,
jt.id_ord,
k.COLUMN_VALUE AS Key,
get_value( jt.vals, k.COLUMN_VALUE ) AS value
FROM j
CROSS APPLY JSON_TABLE(
j.sjson,
'$'
COLUMNS (
id VARCHAR2(20) PATH '$.ID',
id_ord VARCHAR2(30) PATH '$.ID_ORD',
vals VARCHAR2(4000) FORMAT JSON PATH '$.Vals'
)
) jt
CROSS APPLY get_keys( jt.vals ) kWhich outputs:
ID ID_ORD KEY VALUE 1444284517 4255;2187606199 CODE ONB2B3BB8 1444284517 4255;2187606199 DORD 25.04.2021 What is the most efficient way to parse JSON fields in PL/SQL?
You can use a
JSON_TABLEwithOUTER APPLY:SELECT column_1,
column_3,
j.*
FROM my_table m
OUTER APPLY JSON_TABLE(
m.column_2,
'$'
COLUMNS (
dt NUMBER PATH '$.date',
name VARCHAR2(50) PATH '$.name',
age NUMBER PATH '$.age'
)
) j;Or, if you prefer to use an ANSI standard
LATERALjoin rather than the proprietaryOUTER APPLYthen:SELECT column_1,
column_3,
j.*
FROM my_table m
LEFT OUTER JOIN LATERAL (
SELECT *
FROM JSON_TABLE(
m.column_2,
'$'
COLUMNS (
dt NUMBER PATH '$.date',
name VARCHAR2(50) PATH '$.name',
age NUMBER PATH '$.age'
)
)
) j
ON (1 = 1);Which, for the sample data:
CREATE TABLE my_table (COLUMN_1, COLUMN_2, COLUMN_3) AS
SELECT 'test_1212', '{date: 1646240118, name: "John", age: null}', 'test_2311' FROM DUAL UNION ALL
SELECT 'test_998', null, 'test_26351' FROM DUAL UNION ALL
SELECT 'test_56551', '{age: 20}', 'test_3323' FROM DUAL;Both output:
COLUMN_1 COLUMN_3 DT NAME AGE test_1212 test_2311 1646240118 John null test_998 test_26351 null null null test_56551 test_3323 null null 20
Related Topics
Using Backquote/Backticks for MySQL Queries
Sqlite Binding Within String Literal
The Wait Operation Timed Out. Asp
Inserting Data into Hive Table
Generate SQL to Update Primary Key
Convert Access Transform/Pivot Query to SQL Server
SQL Conditional Column Data Return in a Select Statement
How to Select Records Only from Yesterday
MySQL Slow on First Query, Then Fast for Related Queries
Entity Framework: How to Properly Handle Exceptions That Occur Due to SQL Constraints
Parse JSON into Oracle Table Using Pl/Sql
SQL Server - Check to See If Cast Is Possible
Differencebetween Oracle's 'Yy' and 'Rr' Date Mask
Create Table Permission Denied in Database 'Master'
Regular Expression to Remove Comments from SQL Statement