Kishore Kumar Kishore Kumar - 1 year ago 278
JSON Question

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

Answer Source

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)
  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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download