JSON elements parsing into oracle column

Question

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.

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.

Answer 1

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