Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
JSON Query

Hi , I am having a query written in Oracle 12c and i want to convert the query as JSON Query and return the recordset as json value (Eg: "appid" :1, ,"app_name" :msdn ) etc can anyone help me to convert the same and share to me.
Query to Convert
select distinct * from (SELECT ca.app_id, ca.app_name, ca.description, caa.cust_id, caa.cust_application_allow_id, case when caa.privilege = 1 Then 1 else 0 end as privilege
FROM cyl_app ca
inner join cus_app_all caa on caa.app_id = ca.app_id where
caa.cust_id = custid )
union all
select distinct app_id, app_name, description, 0 as cust_id, 0 as cust_application_allow_id, 0 as privilege from
cyl_app where app_id not in (
SELECT ca.app_id FROM cyl_app ca
inner join cus_app_all caa
on caa.app_id = ca.app_id
where caa.cust_id = custid );
Comments
-
It sounds like what you're asking is how to return JSON from your query, rather than asking about how to run a JSON_QUERY, is that correct?
If so, what you are doing is called "JSON Generation", that is, generating JSON from SQL. Without knowing exactly what you're looking to do, my guess that wrapping your query in a JSON_OBJECT() function will get you close to the desired result. E.g.
select JSON_OBJECT('appid' value Q.APP_ID, 'app_name' value Q.APP_NAME, ... RETURNING CLOB) from (...your query here...) Q
If you bundle your UNION ALL query into an in-line view, with an alias (Q in my example above), you can then reference the columns in the JSON_OBJECT function. If you expect large results for each row, you'll need to explicitly say RETURNING CLOB, otherwise you'll get the default of VARCHAR2(4000).
Note that this will return one JSON object per row of the underlying query, i.e. you will get as output:
{"appid":123,"app_name":"MSDN",...}
{...next row...}
{...next row...}
If your objective is to get one giant JSON array as the output, you'll have to feed the JSON_OBJECT output into JSON_ARRAYAGG, an aggregation function that will take in multiple rows and produce one big row. E.g.
select JSON_ARRAYAGG(OBJ returning CLOB) from (select JSON_OBJECT(....) OBJ from (...your query...) Q)
You'll definitely have to use returning CLOB for this if you do it. Also, please note that if you care about the order of the elements in the array, you have to put an ORDER BY inside the JSON_ARRAYAGG operator as described in the documentation, which means you'll have to make those properties available in the select statement that produces the JSON_OBJECT. e.g.
select JSON_ARRAYAGG(OBJ returning CLOB order by FOO) from (select JSON_OBJECT(...) OBJ, Q.FOO from (...your query...) Q)