Home » SQL & PL/SQL » SQL & PL/SQL » How to format JSON column (RDBMS 12C)
| How to format JSON column [message #657193] |
Mon, 31 October 2016 14:31  |
samiraly
Messages: 57
Registered: June 2009
|
Member |
|
|
Hello Experts
i am new to JSON stuff , i have a table where there is a JSON column
SQL> desc ISBR_SERVICE_MANAGEMENT
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
SOURCE_TYPE NOT NULL VARCHAR2(32)
ADD_TMSTMP NOT NULL TIMESTAMP(6)
ADD_ADT_NAME NOT NULL VARCHAR2(32)
CHNG_TMSTMP NOT NULL TIMESTAMP(6)
CHNG_ADT_NAME NOT NULL VARCHAR2(32)
SVC_MGMT_DOCUMENT NOT NULL CLOB
column SVC_MGMT_DOCUMENT is JSON , how could i view column content formated as JSON ?
select ISBR_SERVICE_MANAGEMENT.SVC_MGMT_DOCUMENT from ISBR_SERVICE_MANAGEMENT where rownum=1;
{"CEM_DATA":[{"Interaction ID":"E2-SD013796805","Status":"Closed","Service Recip
ient":"CAPETOWNCSC@SHLR","Open Time":"4/8/2016 16:17","Close Time":"4/8/2016 16:
17","Ms Aff Geo Sites":"CA","Owning Workgroup":"W-INCFLS-SHLR-CEM"},{"Interactio
n ID":"E2-SD013851986","Status":"Closed","Service Recipient":"CAPETOWNCSC@SHLR",
"Open Time":"8/8/2016 22:49","Close Time":"8/8/2016 22:49","Ms Aff Geo Sites":"C
A","Owning Workgroup":"W-INCFLS-SHLR-CEM"},{"Interaction ID":"E2-SD013959411","S
tatus":"Closed","Service Recipient":"CAPETOWNCSC@SHLR","Open Time":"15/08/2016 1
3:17:58","Close Time":"16/08/2016 15:03:07","Ms Aff Geo Sites":"CA","Owning Work
group":"W-INCFLS-SHLR-CEM"},{"Interaction ID":"E2-SD013983425","Status":"Closed"
,"Service Recipient":"CAPETOWNCSC@SHLR","Open Time":"16/08/2016 14:14:51","Close
Time":"16/08/2016 16:46:21","Ms Aff Geo Sites":"CA","Owning Workgroup":"W-INCFL
S-SHLR-CEM"},{"Interaction ID":"E2-SD013987272","Status":"Closed","Service Recip
ient":"CAPETOWNCSC@SHLR","Open Time":"16/08/2016 16:59:55","Close Time":"16/08/2
016 16:59:55","Ms Aff Geo Sites":"CA","Owning Workgroup":"W-INCFLS-SHLR-CEM"}]}
i need to format output into readable JSON format
Thanks in advance .
|
|
|
|
|
|
|
|
| Re: How to format JSON column [message #657196 is a reply to message #657193] |
Mon, 31 October 2016 16:08  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
It's all documented JSON_QUERY:
SQL> create table tbl
2 as
3 select '{"CEM_DATA":[{"Interaction ID":"E2-SD013796805",
4 "Status":"Closed","Service Recipient":"CAPETOWNCSC@SHLR","Open Time":"4/8/2016 16:17",
5 "Close Time":"4/8/2016 16:17","Ms Aff Geo Sites":"CA","Owning Workgroup":"W-INCFLS-SHLR-CEM"},
6 {"Interaction ID":"E2-SD013851986","Status":"Closed","Service Recipient":"CAPETOWNCSC@SHLR",
7 "Open Time":"8/8/2016 22:49","Close Time":"8/8/2016 22:49","Ms Aff Geo Sites":"CA",
8 "Owning Workgroup":"W-INCFLS-SHLR-CEM"},{"Interaction ID":"E2-SD013959411","Status":"Closed",
9 "Service Recipient":"CAPETOWNCSC@SHLR","Open Time":"15/08/2016 13:17:58",
10 "Close Time":"16/08/2016 15:03:07","Ms Aff Geo Sites":"CA","Owning Workgroup":"W-INCFLS-SHLR-CEM"},
11 {"Interaction ID":"E2-SD013983425","Status":"Closed","Service Recipient":"CAPETOWNCSC@SHLR",
12 "Open Time":"16/08/2016 14:14:51","CloseTime":"16/08/2016 16:46:21","Ms Aff Geo Sites":"CA",
13 "Owning Workgroup":"W-INCFLS-SHLR-CEM"},{"Interaction ID":"E2-SD013987272","Status":"Closed",
14 "Service Recipient":"CAPETOWNCSC@SHLR","Open Time":"16/08/2016 16:59:55",
15 "Close Time":"16/08/2016 16:59:55","Ms Aff Geo Sites":"CA","Owning Workgroup":"W-INCFLS-SHLR-CEM"}]}' json_doc
16 from dual
17 /
Table created.
SQL> alter table tbl
2 add constraint is_json
3 check(
4 json_doc is json
5 )
6 /
Table altered.
SQL> set pagesize 200
SQL> select json_query(
2 json_doc,
3 '$'
4 pretty
5 ) formatted_json_doc
6 from tbl
7 /
FORMATTED_JSON_DOC
--------------------------------------------------------------------------------
{
"CEM_DATA" :
[
{
"Interaction ID" : "E2-SD013796805",
"Status" : "Closed",
"Service Recipient" : "CAPETOWNCSC@SHLR",
"Open Time" : "4/8/2016 16:17",
"Close Time" : "4/8/2016 16:17",
"Ms Aff Geo Sites" : "CA",
"Owning Workgroup" : "W-INCFLS-SHLR-CEM"
},
{
"Interaction ID" : "E2-SD013851986",
"Status" : "Closed",
"Service Recipient" : "CAPETOWNCSC@SHLR",
"Open Time" : "8/8/2016 22:49",
"Close Time" : "8/8/2016 22:49",
"Ms Aff Geo Sites" : "CA",
"Owning Workgroup" : "W-INCFLS-SHLR-CEM"
},
{
"Interaction ID" : "E2-SD013959411",
"Status" : "Closed",
"Service Recipient" : "CAPETOWNCSC@SHLR",
"Open Time" : "15/08/2016 13:17:58",
"Close Time" : "16/08/2016 15:03:07",
"Ms Aff Geo Sites" : "CA",
"Owning Workgroup" : "W-INCFLS-SHLR-CEM"
},
{
"Interaction ID" : "E2-SD013983425",
"Status" : "Closed",
"Service Recipient" : "CAPETOWNCSC@SHLR",
"Open Time" : "16/08/2016 14:14:51",
"CloseTime" : "16/08/2016 16:46:21",
"Ms Aff Geo Sites" : "CA",
"Owning Workgroup" : "W-INCFLS-SHLR-CEM"
},
{
"Interaction ID" : "E2-SD013987272",
"Status" : "Closed",
"Service Recipient" : "CAPETOWNCSC@SHLR",
"Open Time" : "16/08/2016 16:59:55",
"Close Time" : "16/08/2016 16:59:55",
"Ms Aff Geo Sites" : "CA",
"Owning Workgroup" : "W-INCFLS-SHLR-CEM"
}
]
}
SQL>
SY.
|
|
|
|
|
|
| Re: How to format JSON column [message #657265 is a reply to message #657195] |
Thu, 03 November 2016 09:04 |
samiraly
Messages: 57
Registered: June 2009
|
Member |
|
|
Michel Cadot wrote on Mon, 31 October 2016 15:46
Why should we help you when you did not feedback and thank people in your previous topics?
Thank you for help that was exactly what i need , that is really helps , i searched the internet but i couldnt find what you have provided here
Again thank toy for help
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 17:21:46 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
