parsing JSON string in oracle

Question

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

Built in JSON support is available from Oracle 12c documenation — Jens Krogsboell
– Jens Krogsboell, Commented Sep 12, 2014 at 8:59
Otherwise I guess your best chance is to use stored java or perhaps pl/sql. Depends on your exact needs - sample code? — Jens Krogsboell
– Jens Krogsboell, Commented Sep 12, 2014 at 9:27

Emmanuel · Accepted Answer · 2014-09-15 07:39:19Z

7

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
)
;

edited Sep 15, 2014 at 7:39

answered Sep 12, 2014 at 17:12

Emmanuel

14.2k12 gold badges53 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kishore Kumar Over a year ago

i need result in a single row separated by comma , not in multiple rows. is it possible ?

Kishore Kumar Over a year ago

,your snippet is very useful to me, but if contactBy contains any special characters then it is not working.

Emmanuel Over a year ago

I modified my answer to fit all your needs :-)

Kishore Kumar · Accepted Answer · 2014-09-15 09:08:55Z

@ 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;
/

Collectives™ on Stack Overflow

parsing JSON string in oracle

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related