CODEWITHSUNDEEP
jsonoracle-databaseoracle-sqldeveloperoracle19c
goldenbutter
goldenbutter

Reputation: 589

JSON elements parsing into oracle column

I have a oracle table where one column has JSON data. I need to extract two elements from the data and want to display as column.

I am adding JSON data as a code sample to test.

create table TEST_TABLE
(id number,
importdata clob);  


insert into TEST_TABLE values (100,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"GEO_METHOD","Value":"96"},{"Name":"HPRCSN","Value":2.7676}]}');
insert into TEST_TABLE values (101,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"HPRCSN","Value":3.04}]}');
insert into TEST_TABLE values (102,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"GEO_METHOD","Value":"96"},{"Name":"HPRCSN","Value":77.1814}]}');
insert into TEST_TABLE values (103,'{"ClassId":30074,"Attributes":[{"Name":"TYPE-SPEC","Value":"SJ;3;1"},{"Name":"HREF","Value":"-1"},{"Name":"HMETHOD","Value":"96"},{"Name":"GEO_METHOD","Value":"-1"},{"Name":"HPRCSN","Value":3.1121}]}');
insert into TEST_TABLE values (105,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"69804"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"} ');
insert into TEST_TABLE values (106,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"73576"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"}]} ');
insert into TEST_TABLE values (107,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"73589"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"}]} ');
insert into TEST_TABLE values (108,'{"ClassId":32000,"Attributes":[{"Name":"ID","Value":"74015"},{"Name":"HREF","Value":"-1"},{"Name":"HPRCSN","Value":"5"}]},{"Name":"HMETHOD","Value":"96"}]} ');
commit;

Now my actual plan was to get two elements out of these data : HMETH and HPRCSN. I want to write a sql which will give me the output like this.

enter image description here

But I faced two problem

  1. Each elements position might not same for each row. So i cannot use fixed position for substr for that.
  2. If the Value of HPRCSN is round then it has "" enclosed and if it is decimal then it comes without "". so all decimal output comes as round integer.

We made some code which is working some of it, but not 100% working because elements position and the decimal values. If anyone have any suggestion to fix this sql it would be so helpful.

 select t1.id
,to_number(regexp_substr(replace(regexp_replace(importdata, '[^,[:digit:]]',''),',,',','),'[^,]+',15)) as HMETH
,to_number(regexp_substr(replace(regexp_replace(importdata, '[^,[:digit:]]',''),',,',','),'[^,]+',18)) as HPRCSN
from TEST_TABLE t1;

Here is my output which is wrong for some rows because of the position.

enter image description here

Upvotes: 0

Views: 79

Answers (1)

MT0
MT0

Reputation: 167981

Never use regular expressions to parse HTML JSON; use a proper parser.

You can use JSON_TABLE to extract the name-value pairs:

select id,
       classid,
       name,
       value
from   TEST_TABLE t1
       CROSS APPLY JSON_TABLE(
         t1.importdata,
         '$'
         COLUMNS (
           classid NUMBER PATH '$.ClassId',
           NESTED PATH '$.Attributes[*]' COLUMNS (
             name  VARCHAR2(20) PATH '$.Name',
             value VARCHAR2(20) PATH '$.Value'
           )
         )
       );

Which, for your sample data, outputs:

ID CLASSID NAME VALUE
100 30074 TYPE-SPEC SJ;3;1
100 30074 HREF -1
100 30074 HMETHOD 96
100 30074 GEO_METHOD 96
100 30074 HPRCSN 2.7676
101 30074 TYPE-SPEC SJ;3;1
101 30074 HREF -1
101 30074 HMETHOD 96
101 30074 HPRCSN 3.04
102 30074 TYPE-SPEC SJ;3;1
102 30074 HREF -1
102 30074 HMETHOD 96
102 30074 GEO_METHOD 96
102 30074 HPRCSN 77.1814
103 30074 TYPE-SPEC SJ;3;1
103 30074 HREF -1
103 30074 HMETHOD 96
103 30074 GEO_METHOD -1
103 30074 HPRCSN 3.1121
105 32000 ID 69804
105 32000 HREF -1
105 32000 HPRCSN 5
106 32000 ID 73576
106 32000 HREF -1
106 32000 HPRCSN 5
107 32000 ID 73589
107 32000 HREF -1
107 32000 HPRCSN 5
108 32000 ID 74015
108 32000 HREF -1
108 32000 HPRCSN 5

If you want the values in columns (instead of rows) then use PIVOT:

SELECT *
FROM   (
  select id,
         classid,
         name,
         value
  from   TEST_TABLE t1
         CROSS APPLY JSON_TABLE(
           t1.importdata,
           '$'
           COLUMNS (
             classid NUMBER PATH '$.ClassId',
             NESTED PATH '$.Attributes[*]' COLUMNS (
               name  VARCHAR2(20) PATH '$.Name',
               value VARCHAR2(20) PATH '$.Value'
             )
           )
         ) j
)
PIVOT (
  MAX(value) FOR name IN (
    'ID' AS idvalue,
    'HREF' AS href,
    'GEO_METHOD' AS geomethod,
    'HPRCSN' AS hprcsn,
    'HMETHOD' AS hmethod
  )
);

Which outputs:

ID CLASSID IDVALUE HREF GEOMETHOD HPRCSN HMETHOD
100 30074 null -1 96 2.7676 96
107 32000 73589 -1 null 5 null
108 32000 74015 -1 null 5 null
101 30074 null -1 null 3.04 96
106 32000 73576 -1 null 5 null
103 30074 null -1 -1 3.1121 96
105 32000 69804 -1 null 5 null
102 30074 null -1 96 77.1814 96

fiddle

Upvotes: 1

Related Questions