CODEWITHSUNDEEP
jsonoracle-database
Kishore Kumar
Kishore Kumar

Reputation: 939

parsing JSON string in oracle

i have JSON string in one column in oracle 10g database like 

[{"id":"1","contactBy":"Rajesh Kumar"},{"id":"2","contactBy":"Rakesh Kumar"}]

I have to get the value for ContactBy in that column for one of the reports.

is there any built in function to parse the JSON string in Oracle 10g or any user defined funciton to parse the String

Upvotes: 1

Views: 16787

Answers (2)

Kishore Kumar
Kishore Kumar

Reputation: 939

@ Emmanuel your code is really helped a lot, thank you very much. but your query is taking too much of time, so i changed to a function , which will return the required values.

CREATE OR REPLACE FUNCTION SFGETCRCONTACTBY(INCRID NUMBER) RETURN VARCHAR2 AS
TEMPINT NUMBER :=0;
OUTPUT VARCHAR2(10000) ;
TEMPVAR VARCHAR2(1000);

BEGIN

SELECT   REGEXP_COUNT(CR_CONTACT_BY, '"contactBy":\S*(".*?")')
INTO TEMPINT 
FROM T_LOAN_REQUEST_MARKET WHERE CR_ID=INCRID;
WHILE TEMPINT > 0
LOOP
SELECT REGEXP_REPLACE(REGEXP_SUBSTR(CR_CONTACT_BY, '"contactBy":\S*(".*?")', 1,TEMPINT),  '"contactBy":\S*"(.*?)"', '\1', 1, 1) INTO TEMPVAR
FROM T_LOAN_REQUEST_MARKET WHERE CR_ID=INCRID;
IF OUTPUT IS  NULL  THEN
    OUTPUT :=   TEMPVAR;
 ELSE 
    OUTPUT :=  OUTPUT ||',' || TEMPVAR;
END IF;

TEMPINT := TEMPINT-1;
END LOOP;

RETURN OUTPUT;
END;
/

Upvotes: 0

Emmanuel
Emmanuel

Reputation: 14209

As said by Jens in comments, JSON support is only available from 12c, but you can use regular expressions as a workaround to get what you want:

select regexp_replace(regexp_substr('[{"id": "1", "contactBy":"Rajesh Kumar"},{"id": "2","contactBy": "Emmanuel Test"}]',
                                    '"contactBy":\s*("(\w| )*")', 1, level),
                     '"contactBy":\s*"((\w| )*)"', '\1', 1, 1) contact
from dual
connect by regexp_substr('[{"id": "1","contactBy":"Rajesh Kumar"},{"id": "2","contactBy": "Emmanuel Test"}]', '"contactBy":\s*("(\w| )*")', 1, level) is not null
;

EDIT : request modified to take both special characters and display answers in a single row:

select listagg(contact, ', ') within group (order by lev)
from
(
  select regexp_replace(regexp_substr('[{"id": "1", "contactBy":"Rajesh Kumar"},{"id": "2","contactBy": "Emmanuel Test+-"}]',
                                      '"contactBy":\s*(".*?")', 1, level),
                       '"contactBy":\s*"(.*?)"', '\1', 1, 1) contact, level lev
  from dual
  connect by regexp_substr('[{"id": "1","contactBy":"Rajesh Kumar"},{"id": "2","contactBy": "Emmanuel Test+-"}]', '"contactBy":\s*(".*?")', 1, level) is not null
)
;

Upvotes: 7

Related Questions