- 3,715,598 Users
- 2,242,806 Discussions
- 7,845,454 Comments
Forum Stats
Discussions
Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 1.6K Databases
- 465 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 416 SQLcl
- 42 SQL Developer Data Modeler
- 184.8K SQL & PL/SQL
- 21K SQL Developer
- 1.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 8 DevOps
- 3K QA/Testing
- 247 Java
- 5 Java Learning Subscription
- 10 Database Connectivity
- 66 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 125 LiveLabs
- 30 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
Oracle sql split text into rows
Hello Everyone ,
I have a clob column which has value like this :
{
adam smith
class:abcd
}
{
xxxyyyy
class:abcd
}
{
zzzz
class:abcd
}
class:abcd characters are common all between {}
İ want to split into rows after each class:abcd} :
row 1:{
adam smith
class:abcd
}
row2:{
adam smith
class:abcd
}
row3:{
adam smith
class:abcd
}
I tried to use connect by but i didnt do it exactly.
How can i split this text into rows ?
Regards,
Best Answer
-
Again, your JSON isn't valid. It's an array and you don't have an array marker around it or a comma between the array items. You need to take more care in your posts.
with data(jsn) as ( select '[ { "_id":"1000099721", "id":[ { "value":"55555", "schemeName":"MSISDN", "schemeAgencyName":"xx" }, { "value":"12416408", "schemeName":"CustomerId", "schemeAgencyName":"xx" }, { "value":"441630", "schemeName":"OTP", "schemeAgencyName":"xx" } ], "_class":"model.salesorder.SalesOrderVBO" } ,{ "_id":"1000099721", "id":[ { "value":"6666", "schemeName":"MSISDN", "schemeAgencyName":"xx" }, { "value":"12416408", "schemeName":"CustomerId", "schemeAgencyName":"xx" }, { "value":"441630", "schemeName":"OTP", "schemeAgencyName":"xx" }, { "value":"ffa357ee-9759-42ab-8a98-30ea9d410319", "schemeName":"ShoppingCartId", "schemeAgencyName":"xx" }, { "value":"1000099721", "schemeName":"OrderId", "schemeAgencyName":"xx" } ], "_class":"model.salesorder.SalesOrderVBO" } ]' from dual ) select c.* from data d, json_table ( d.jsn , '$[*]' columns ( order_pos for ordinality , id varchar2(30) path '$."_id"' , nested path '$[*]' columns ( content varchar2(4000 byte) format json path '$' ) ) ) c / ORDER_POS ID CONTENT 1 1000099721 {"_id":"1000099721","id":[{"value":"55555","schemeName":"MSISDN","schemeAgencyName":"xx"},{"value":"12416408","schemeName":"CustomerId","schemeAgencyName":"xx"},{"value":"441630","schemeName":"OTP","schemeAgencyName":"xx"}],"_class":"model.salesorder.SalesOrderVBO"} 2 1000099721 {"_id":"1000099721","id":[{"value":"6666","schemeName":"MSISDN","schemeAgencyName":"xx"},{"value":"12416408","schemeName":"CustomerId","schemeAgencyName":"xx"},{"value":"441630","schemeName":"OTP","schemeAgencyName":"xx"},{"value":"ffa357ee-9759-42ab-8a98-30ea9d410319","schemeName":"ShoppingCartId","schemeAgencyName":"xx"},{"value":"1000099721","schemeName":"OrderId","schemeAgencyName":"xx"}],"_class":"model.salesorder.SalesOrderVBO"}
Answers
-
This looks like a half house JSON document. Is this actual document JSON?
-
Here you go, something like this. I'm splitting on the braces, rather than the content.
with data(clb) as ( select '{ adam smith class:abcd } { xxxyyyy class:abcd } { zzzz class:abcd }' from dual ) , sections(num, clb, start_, end_) as ( select level, clb, instr(clb, '{', 1, level), instr(clb, '}', 1, level) from data connect by instr(clb, '}', 1, level) > 0 ) select num, substr(clb, start_, end_-start_+1) str from sections -
Hello Paulzip,
Your code is working correctly but i couldn't explain my problem.Yes it is an actual json .Here is a sample jscon code. i have a json file like this . When i tried to insert this it raised check constraint error . Becasue there are two "_id" values .İf i insert id by id not in same time it worked .İ want to split braches every "class_..."
{
"_id" : "1000099721",
"id" : [
{
"value" : "55555",
"schemeName" : "MSISDN",
"schemeAgencyName" : "xx"
},
{
"value" : "12416408",
"schemeName" : "CustomerId",
"schemeAgencyName" : "xx"
},
{
"value" : "441630",
"schemeName" : "OTP",
"schemeAgencyName" : "xx"
}
],
"_class" : "model.salesorder.SalesOrderVBO"
}
{
"_id" : "1000099721",
"id" : [
{
"value" : "6666",
"schemeName" : "MSISDN",
"schemeAgencyName" : "xx"
},
{
"value" : "12416408",
"schemeName" : "CustomerId",
"schemeAgencyName" : "xx"
},
{
"value" : "441630",
"schemeName" : "OTP",
"schemeAgencyName" : "xx"
},
{
"value" : "ffa357ee-9759-42ab-8a98-30ea9d410319",
"schemeName" : "ShoppingCartId",
"schemeAgencyName" : "xx"
},
{
"value" : "1000099721",
"schemeName" : "OrderId",
"schemeAgencyName" : "xx"
}
],
"_class" : "model.salesorder.SalesOrderVBO"
}
-
So, as it's actual JSON you're dealing with, have you considered using the Oracle built-in functionality for JSON data?
-
Then why didn't you say it was JSON rather than post an example that wasn't? It just wastes people, like myself's time.
Which Oracle version?
-
Oracle 12.2
-
Again, your JSON isn't valid. It's an array and you don't have an array marker around it or a comma between the array items. You need to take more care in your posts.
with data(jsn) as ( select '[ { "_id":"1000099721", "id":[ { "value":"55555", "schemeName":"MSISDN", "schemeAgencyName":"xx" }, { "value":"12416408", "schemeName":"CustomerId", "schemeAgencyName":"xx" }, { "value":"441630", "schemeName":"OTP", "schemeAgencyName":"xx" } ], "_class":"model.salesorder.SalesOrderVBO" } ,{ "_id":"1000099721", "id":[ { "value":"6666", "schemeName":"MSISDN", "schemeAgencyName":"xx" }, { "value":"12416408", "schemeName":"CustomerId", "schemeAgencyName":"xx" }, { "value":"441630", "schemeName":"OTP", "schemeAgencyName":"xx" }, { "value":"ffa357ee-9759-42ab-8a98-30ea9d410319", "schemeName":"ShoppingCartId", "schemeAgencyName":"xx" }, { "value":"1000099721", "schemeName":"OrderId", "schemeAgencyName":"xx" } ], "_class":"model.salesorder.SalesOrderVBO" } ]' from dual ) select c.* from data d, json_table ( d.jsn , '$[*]' columns ( order_pos for ordinality , id varchar2(30) path '$."_id"' , nested path '$[*]' columns ( content varchar2(4000 byte) format json path '$' ) ) ) c / ORDER_POS ID CONTENT 1 1000099721 {"_id":"1000099721","id":[{"value":"55555","schemeName":"MSISDN","schemeAgencyName":"xx"},{"value":"12416408","schemeName":"CustomerId","schemeAgencyName":"xx"},{"value":"441630","schemeName":"OTP","schemeAgencyName":"xx"}],"_class":"model.salesorder.SalesOrderVBO"} 2 1000099721 {"_id":"1000099721","id":[{"value":"6666","schemeName":"MSISDN","schemeAgencyName":"xx"},{"value":"12416408","schemeName":"CustomerId","schemeAgencyName":"xx"},{"value":"441630","schemeName":"OTP","schemeAgencyName":"xx"},{"value":"ffa357ee-9759-42ab-8a98-30ea9d410319","schemeName":"ShoppingCartId","schemeAgencyName":"xx"},{"value":"1000099721","schemeName":"OrderId","schemeAgencyName":"xx"}],"_class":"model.salesorder.SalesOrderVBO"}
