how to solve below query ?

Hi expert i have below query
json Data: {"1":{"YBG":1,"BNT":"}{}{dg}"},"2":{"YBG":1,"BNT":"vc}{}}vm"},"3":{"YBG":1,"BNT":"vc{}}vm"}}

SELECT *
  FROM (SELECT
  	REGEXP_REPLACE(
                   REGEXP_SUBSTR(
                       :JSONdata,
                       '"[0-9]+".+?"}',
                       1,
                       LEVEL
                   )
                   ,
                   '^[{"]+|["}]+$'
               )
                   AS rec
          FROM DUAL
        CONNECT BY LEVEL <= 11)
 WHERE rec IS NOT NULL;

Open in new window

output:
1":{"YBG":1,"BNT":
2":{"YBG":1,"BNT":"vc}{}}vm
3":{"YBG":1,"BNT":"vc{}}vm

expected output:
1":{"YBG":1,"BNT":}{}{dg
2":{"YBG":1,"BNT":"vc}{}}vm
3":{"YBG":1,"BNT":"vc{}}vm

Regards
Thomos
deve_thomosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexander Eßer [Alex140181]Software DeveloperCommented:
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);

Open in new window

0
slightwv (䄆 Netminder) Commented:
0
sdstuberCommented:
SELECT *
  FROM (SELECT
      REGEXP_REPLACE(
                   REGEXP_SUBSTR(
                       :JSONdata,
                       '"[0-9]+".+?"},|"[0-9]+".+?"}',
                       1,
                       LEVEL
                   )
                   ,
                   '^[{"]+|["},]+$'
               )
                   AS rec
          FROM DUAL
        CONNECT BY LEVEL <= 11)
 WHERE rec IS NOT NULL;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

deve_thomosAuthor Commented:
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
0
sdstuberCommented:
>>>> 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.
0
deve_thomosAuthor Commented:
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.