How to turn generate JSON with Oracle SQL

Question

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

For JSON support on Oracle DB's you can refer to docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/… . If your db does not support these features, you need to find a custom JSON formatter — Eralper
– Eralper, Commented Apr 17, 2018 at 14:00
Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented Apr 17, 2018 at 14:03

Alex Poole · Accepted Answer · 2018-04-17 15:08:17Z

3

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.

edited Apr 17, 2018 at 15:08

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

answered Apr 17, 2018 at 14:53

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

thatjeffsmith Over a year ago

thanks for the assist @alex-poole - the /* always gets me

Alex Poole Over a year ago

No worries, me too - usually when writing SQL*Plus or SQL*Loader, though I've now started escaping that * offline which confuses people... This is nice BTW. I'm not sure the formatting options (this version and set sqlformat), or the SQLcl equivalents, are widely known or appreciated so it's always good when you have a chance to plug them *8-)

thatjeffsmith Over a year ago

ps @AlexPoole we just open sourced the code that does that formatting bits, it's java and on github if you're curious github.com/oracle/dbtools-commons/tree/master/common

Alex Poole Over a year ago

Ooh, nice. The parsing stuff looks interesting too, there have been questions about how to parse scripts into statement. Thanks!

thatjeffsmith Over a year ago

@FatihKara I would only say or add, SQLDev is free, it comes WITH the database, and there's no 'installer' - you just unzip it.

|

kfinity · Accepted Answer · 2018-04-17 14:10:24Z

0

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:

PL/JSON (open source library)
APEX_JSON (library included with Oracle APEX)

answered Apr 17, 2018 at 14:10

kfinity

9,1411 gold badge15 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to turn generate JSON with Oracle SQL

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related