JSON Query

VSat

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 );

   

dmcmahon-Oracle

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)