0

I'm using Oracle query with regular expression and oracle regexp_substr to extract json fields from JSON string.

whene I try to get the value of code key it working well and its value, but whene i try to get the value of results key it return null.

I'm using this query:

select regexp_replace(regexp_substr('{"code":"001","message":"success","transactionId":437,"results":{"name":"osama"}}','"results":\s*("(\w| )*")', 1, level), '"results":\s*"((\w| )*)"', '\1', 1, 1) results
 from dual
connect by regexp_substr('{"code":"001","message":"success","transactionId":437,"results":{"name":"osama"}}', '"results":\s*("(\w| )*")', 1, level) is not null;

What is wrong in this query? Thanks

Osama Jetawe
  • 2,697
  • 6
  • 24
  • 40
  • That is because \w only matches alphanumeric characters. The string you are trying to match inside results is - `{"name":"osama"}` which has curly braces,double quotes, and colon. Are you on oracle 12c? – ruudvan Apr 07 '16 at 14:37
  • Thanks @ruudvan ,No I'm not using oracle 12c , can you help me with correct expression – Osama Jetawe Apr 07 '16 at 14:52

1 Answers1

1

Allowing for the different data format. This results in {"name":"osama"} which I hope is what you want:

select regexp_replace(regexp_substr('{"code":"001","message":"success","transactionId":437,"results":{"name":"osama"}}','"results":\s*{"(.*| )*"\}', 1, level), '"results":\s*(\{.*\})', '\1', 1, 1) results
 from dual
connect by regexp_substr('{"code":"001","message":"success","transactionId":437,"results":{"name":"osama"}}', '"results":\{(.*)"\}', 1, level) is not null;
Gary_W
  • 9,933
  • 1
  • 22
  • 40