how to solve below query ?

What about these 2 approaches?!

select *
  from (select regexp_replace(regexp_substr(:JSONdata,
                                            '"[0-9]+".+?"},' || case level
                                              when 1 then
                                               '^'
                                              else
                                               null
                                            end || '*?',
                                            1,
                                            level),
                              '^[{"]+|["}]+$') as rec
          from dual
        connect by level <= 11)
 where rec is not null;

select single_element,
       element_no
  from (select regexp_substr(str, '[^ ~'']+', 1, level) || '}' as single_element,
               level as element_no
          from (select rownum as id,
                       regexp_replace(rtrim(ltrim(:JSONdata, '{'), '}'), '},', '~') str
                  from dual)
        connect by nocycle instr(str, '~', 1, level - 1) > 0
               and prior dbms_random.value is not null);

Select allOpen in new window

What is different in this question than your other two on this subject:
https://www.experts-exchange.com/questions/28481663/How-to-get-this-query.html
https://www.experts-exchange.com/questions/28482409/How-get-this-query.html

Hi ststuber,
see it is working some scenario,
but the problem is  
suppose  json data is like this
{"1":{"ACC":1,"CNT":"sdvsdvsdvdsvsdvsdvsdvdsvsdvdsvdsvdsv"}}
it is giving expected result
but if my  json data is like below
{"1":{"ACC":1,"CNT":"}sdvsdvsdvdsvsdvsdvsdvdsvsdvdsvdsvdsv"}}
it is not giving proper result
we need to make dyanamic as a result it should work every  json scenario
Regards'
Thomos

>>>> as a result it should work every  json scenario

hahaha,  that's funny   - no way that can happen.

If you want parsing of anything: json, xml, csv, natural language text, etc - the rules for parsing are dependent on a specific input set.

So, every time you change the structure of the input data you need a new set of parsing rules.

This particular series of questions is special in that you (not oracle) knows the content is json and hence nested entities.
So, what you see as a simple A:B syntax,  oracle doesn't.   It only sees a string of characters.  So you have to teach it, but the rules for identifying A and B are going to be based on the placement of {}, ", [], and commas.  However, nesting and interweaving them makes it difficult to identify them for a single string and impossible to do for any string within the context of normal sql.

If you upgrade to 12.1.0.2 it will be A LOT easier.  Any version less than that and you'll need to either write a custom sql for the specific structure you're trying to use.  Or, get a json parser package (pl/sql, java, c) that you can use from within oracle and then use that.

Thanks a lot