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 Go to next message
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 #657194 is a reply to message #657193] Mon, 31 October 2016 14:37 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
http://www.oaktable.net/content/oracle-12c-sql-%E2%80%93-using-json

>i need to format output into readable JSON format
machine readable?

provide example of what you desire the results should be.
Re: How to format JSON column [message #657195 is a reply to message #657193] Mon, 31 October 2016 15:46 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why should we help you when you did not feedback and thank people in your previous topics?

Re: How to format JSON column [message #657196 is a reply to message #657193] Mon, 31 October 2016 16:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2710
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 #657264 is a reply to message #657194] Thu, 03 November 2016 09:02 Go to previous messageGo to next message
samiraly
Messages: 57
Registered: June 2009
Member

BlackSwan wrote on Mon, 31 October 2016 14:37
http://www.oaktable.net/content/oracle-12c-sql-%E2%80%93-using-json

>i need to format output into readable JSON format
machine readable?

provide example of what you desire the results should be.
Hello , So sorry for the inconvenience , i didn't have the chance to check the updates , thanks for help Smile
Re: How to format JSON column [message #657265 is a reply to message #657195] Thu, 03 November 2016 09:04 Go to previous message
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
Previous Topic: Column dropped
Next Topic: Query Help
Goto Forum:
  


Current Time: Sat Feb 24 09:18:56 CST 2018

Total time taken to generate the page: 0.01466 seconds