CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
navid sedigh
navid sedigh

Reputation: 281

convert output of a query to json in oracle 12c

I have a query, the output is like this:

    1  2 3 4 5 6 7 8 9 10 11 12 13
    -  - - - - - - - - -   -  - -    
    40 20 22 10 0 0 0 0 0 0 0 0 0

I want to convert the output to one column and the column is like below:

    output
    -----------
 {"1":40,"2":20,"3":22,"4":10,"5":0,"6":0,"7":0,"8":0,"9":0,"10":0,"11":0,"12":0,"13":0}

Upvotes: 1

Views: 5960

Answers (2)

thatjeffsmith
thatjeffsmith

Reputation: 22427

You can use the JSON formatting 'trick' in SQL Developer.

enter image description here

Full scenario:

CREATE TABLE JSON_SO (
    "1"   INTEGER,
    "2"   INTEGER,
    "3"   INTEGER,
    "4"   INTEGER,
    "5"   INTEGER,
    "6"   INTEGER
);

INSERT INTO JSON_SO VALUES (
    40,
    20,
    22,
    10,
    0,
    0
);

select /*json*/ * from json_so;

And the output when executing with F5 (Execute as Script):

{
   "results":[
      {
         "columns":[
            {
               "name":"1",
               "type":"NUMBER"
            },
            {
               "name":"2",
               "type":"NUMBER"
            },
            {
               "name":"3",
               "type":"NUMBER"
            },
            {
               "name":"4",
               "type":"NUMBER"
            },
            {
               "name":"5",
               "type":"NUMBER"
            },
            {
               "name":"6",
               "type":"NUMBER"
            }
         ],
         "items":[
            {
               "1":40,
               "2":20,
               "3":22,
               "4":10,
               "5":0,
               "6":0
            }
         ]
      }
   ]
}

Note that the JSON output happens client-side via SQL Developer (this also works in SQLcl) and I formatted the JSON output for display here using https://jsonformatter.curiousconcept.com/

This will work with any version of Oracle Database that SQL Developer supports, while the JSON_OBJECT() function was introduced in Oracle Database 12cR2 - if you want to have the DB format the result set to JSON for you.

Upvotes: 7

shakhawat
shakhawat

Reputation: 2727

If you want Oracle DB server to return the result as JSON, you can do query as below -

 SELECT JSON_OBJECT ('1' VALUE col1, '2' VALUE col2, '3' VALUE col3) FROM table

Upvotes: 3

Related Questions