[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to solve below query ?

Posted on 2014-08-14
6
Medium Priority
?
298 Views
Last Modified: 2014-08-17
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
0
Comment
Question by:deve_thomos
6 Comments
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40260244
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40260370
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40260392
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:deve_thomos
ID: 40260610
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40260791
>>>> 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
 

Author Closing Comment

by:deve_thomos
ID: 40266441
Thanks a lot
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses
Course of the Month18 days, 10 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question