convert output of a query to json in oracle 12c

Question

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}

thatjeffsmith · Accepted Answer · 2019-01-16 15:54:19Z

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

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.

you can also use SET SQLFORMAT json...or csv, insert, html, delimited, loader, xml

shakhawat · Accepted Answer · 2019-01-16 15:53:40Z

3

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

edited Jan 16, 2019 at 15:53

answered Jan 16, 2019 at 15:35

shakhawat

2,7231 gold badge24 silver badges37 bronze badges

6 Comments

shakhawat Over a year ago

which SqlDeveloper version are you using?

navid sedigh Over a year ago

Version 18.2.0.183

shakhawat Over a year ago

Can you try this way? SELECT JSON_OBJECT ('1' VALUE col1, '2' VALUE col2, '3' VALUE col3) FROM table;

thatjeffsmith Over a year ago

using the json comment, with f5, the resultset will be formatted as json client side when executed in SQL Developer - if you use json_object() the db will do the json-ification work

navid sedigh Over a year ago

@shakhawat : SELECT JSON_OBJECT ('1' VALUE col1, '2' VALUE col2, '3' VALUE col3) FROM table; It works , thank you please edit your answer and I accept your question as a correct answer

|

Collectives™ on Stack Overflow

convert output of a query to json in oracle 12c

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related