CODEWITHSUNDEEP
sqljsonoracle-database
F K
F K

Reputation: 13

How to turn generate JSON with Oracle SQL

I am trying to get JSON string using Oracle

My Oracle version is 12.1.0.2.0 and as I can't upgrade, I can't use JSON_ARRAYAGG.

The Json I want is exactly same with the picture

Upvotes: 1

Views: 9018

Answers (2)

thatjeffsmith
thatjeffsmith

Reputation: 22447

A Client Based Solution, where client is Oracle SQL Developer.

Query:

SELECT /*json*/ ID, NAME CUST_NAME, ADDRESS,
 CURSOR(
  SELECT ACCOUNT_ID,
         NAME ACCOUNT_NAME,
         BALANCE
  FROM ACCOUNTS
  WHERE CUST.ID = CUSTOMER_ID) AS "accounts",
  '../customers/' || ID || '/pic' AS "$signature"
FROM customers cust

The interesting part of this is the /*json*/ bit. That tells our script engine to take the results and format to JSON. It also supports csv, insert statements, xml, html, etc.

The query returns 3 rows from my table, and an accounts bit, which is some nested results per row via the CURSOR bit of the query.

When executed via F5 (execute as script), SQL Developer returns this JSON

{  
   "results":[  
      {  
         "columns":[  
            {  
               "name":"ID",
               "type":"NUMBER"
            },
            {  
               "name":"CUST_NAME",
               "type":"VARCHAR2"
            },
            {  
               "name":"ADDRESS",
               "type":"VARCHAR2"
            },
            {  
               "name":"accounts",
               "type":"REFCURSOR"
            },
            {  
               "name":"$signature",
               "type":"VARCHAR2"
            }
         ],
         "items":[  
            {  
               "id":1,
               "cust_name":"Jeff",
               "address":"101 Maple Ln",
               "accounts":[  
                  {  
                     "account_id":100,
                     "account_name":"College Fund",
                     "balance":25.99
                  },
                  {  
                     "account_id":101,
                     "account_name":"NewCar",
                     "balance":30000
                  }
               ],
               "$signature":"..\/customers\/1\/pic"
            },
            {  
               "id":2,
               "cust_name":"Kris",
               "address":"911 Pine Dr",
               "accounts":[  
                  {  
                     "account_id":200,
                     "account_name":"Checking",
                     "balance":42.25
                  },
                  {  
                     "account_id":201,
                     "account_name":"Savings",
                     "balance":64000
                  }
               ],
               "$signature":"..\/customers\/2\/pic"
            },
            {  
               "id":3,
               "cust_name":"Colm",
               "address":"404 Irish Corner",
               "accounts":[  
                  {  
                     "account_id":300,
                     "account_name":"Potatoes",
                     "balance":2500.75
                  },
                  {  
                     "account_id":301,
                     "account_name":"Speeding Tickets",
                     "balance":1900
                  }
               ],
               "$signature":"..\/customers\/3\/pic"
            }
         ]
      }
   ]
}

We also have a mid-tier solution, Oracle REST Data Services. This allows you to create a RESTful Service with a GET handler to run SQL or a PL/SQL block, where the results are returned in JSON.

Upvotes: 3

kfinity
kfinity

Reputation: 9091

JSON support in Oracle is pretty limited before 12.2. This blog has posts on 4 different ways to generate JSON in Oracle 12.1.

The two that are relatively easy to do in plain PL/SQL are:

Upvotes: 0

Related Questions