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

x
Solved

# how to solve below query ?

Posted on 2014-08-14
Medium Priority
298 Views
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;
``````
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
Question by:deve_thomos

LVL 14

Expert Comment

ID: 40260244
``````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);
``````
0

LVL 78

Expert Comment

ID: 40260370
0

LVL 74

Accepted Solution

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

Author Comment

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

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

ID: 40266441
Thanks a lot
0

## Featured Post

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