Home » SQL & PL/SQL » SQL & PL/SQL » Help need in writing query to get JSON format output (Oracle 19c)
Help need in writing query to get JSON format output [message #686905] Mon, 30 January 2023 21:24 Go to next message
pr76666
Messages: 2
Registered: January 2023
Junior Member
Hi, I need help in writing a query to get output like following.

{"Ename" : "SMITH",
"Custom" : {
"CO" : { "EMPNO": 101,
"JOB": "CLERK"
},
"QC" : { "SAL": 800,
"DEPTNO": 20
}
}
}
Table structure and values are like below.
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7369, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
800, 20);
Re: Help need in writing query to get JSON format output [message #686909 is a reply to message #686905] Tue, 31 January 2023 01:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
Unfortunately, I am using Oracle 12.1 when this had to be done the hard way. Apparently, starting with Oracle 12.2 there are some built-in functions to help with this. Please see the link below for syntax and examples.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-6C3441E8-4F02-4E95-969C-BBCA6BDBBD9A
Re: Help need in writing query to get JSON format output [message #686910 is a reply to message #686905] Tue, 31 January 2023 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In 19c (your version, thank to mention it) you can do:
MIKJ3DB1> select json_object (
  2    'Ename' : ename,
  3    'Custom' : json_object (
  4       'CO' : json_object (
  5         'EMPNO' : empno,
  6         'JOB' : job ),
  7       'QC' : json_object (
  8         'SAL' : sal,
  9         'DEPTNO' : deptno )
 10       )
 11     ) res
 12  from emp
 13  where ename = 'SMITH'
 14  /
RES
----------------------------------------------------------------------------------------------
{"Ename":"SMITH","Custom":{"CO":{"EMPNO":7369,"JOB":"CLERK"},"QC":{"SAL":800,"DEPTNO":20}}}

The hard way Barbara mentioned and is available in all versions is:
SQL> select '{"Ename" : "'||ename||'",
  2    "Custom" : {
  3      "CO" : {"EMPNO" : '||empno||',
  4              "JOB" : "'||job||'"
  5             },
  6      "QC" : {"SAL" : '||sal||',
  7              "DEPTNO" : ||deptno||
  8             }
  9               }
 10  }' res
 11  from emp
 12  where ename = 'SMITH'
 13  /
RES
--------------------------------------------------
{"Ename" : "SMITH",
  "Custom" : {
    "CO" : {"EMPNO" : 7369,
            "JOB" : "CLERK"
           },
    "QC" : {"SAL" : 800,
            "DEPTNO" : ||deptno||
           }
             }
}
Re: Help need in writing query to get JSON format output [message #686922 is a reply to message #686910] Thu, 02 February 2023 18:58 Go to previous messageGo to next message
pr76666
Messages: 2
Registered: January 2023
Junior Member
Thanks a lot. its working.

Could you please help me on below JSON format.

{
"DeptNo" : 20 ,
"EmployeeDetails" : [
{
"Ename" : "Smith" ,
"Salary" : 800,
"EmpN" : 7369
},
{
"Ename" : "SCOTT" ,
"Salary" : 3000,
"EmpN" : 7788
},
{
"Ename" : "ADAMS",
"Salary" : 1100,
"EmpN" : 7876
}
]
}


Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7369, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
800, 20);

Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('12/09/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
3000, 20);

Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7876, 'ADAMS', 'CLERK', 7788, TO_DATE('01/12/1983 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1100, 20);



I have tried like below to get "EmployeeDetails" array filed value, but it's not working.

select json_arryagg(json_object('Ename' value ename,
'Salary'value sal,
'EmpN' value empno))
from emp
multiple rows coming

"
Re: Help need in writing query to get JSON format output [message #686923 is a reply to message #686922] Thu, 02 February 2023 21:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
Perhaps something like the following will work. I don't have a version that I can test it on. I am just trying to adapt examples.

select json_object(
       'DeptNo' : deptno,
       'EmployeeDetails' : 
       (select json_arrayagg(json_object('Ename' : ename,
                                         'Salary' : sal,
                                         'EmpN' : empno))
        from   emp e
        where  e.deptno = d.deptno))
from   dept d
/

[Updated on: Thu, 02 February 2023 22:03]

Report message to a moderator

Re: Help need in writing query to get JSON format output [message #686924 is a reply to message #686922] Fri, 03 February 2023 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Barbara suggestion is correct:
MIKJ3DB1> set recsepchar '-' recsep each
MIKJ3DB1> select json_object(
  2         'DeptNo' : deptno,
  3         'EmployeeDetails' :
  4         (select json_arrayagg(json_object('Ename' : ename,
  5                                           'Salary' : sal,
  6                                           'EmpN' : empno))
  7          from   emp e
  8          where  e.deptno = d.deptno))
  9  from   dept d
 10  /
JSON_OBJECT('DEPTNO':DEPTNO,'EMPLOYEEDETAILS':(SELECTJSON_ARRAYAGG(JSON_OBJECT('ENAME':ENAME,'SALARY':SAL,'EMPN':EMPNO))
------------------------------------------------------------------------------------------------------------------------
{"DeptNo":10,"EmployeeDetails":[{"Ename":"KING","Salary":5000,"EmpN":7839},{"Ename":"CLARK","Salary":2450,"EmpN":7782},{
"Ename":"MILLER","Salary":1300,"EmpN":7934}]}
------------------------------------------------------------------------------------------------------------------------
{"DeptNo":20,"EmployeeDetails":[{"Ename":"JONES","Salary":2975,"EmpN":7566},{"Ename":"SCOTT","Salary":3000,"EmpN":7788},
{"Ename":"FORD","Salary":3000,"EmpN":7902},{"Ename":"SMITH","Salary":800,"EmpN":7369},{"Ename":"ADAMS","Salary":1100,"Em
pN":7876}]}
------------------------------------------------------------------------------------------------------------------------
{"DeptNo":30,"EmployeeDetails":[{"Ename":"BLAKE","Salary":2850,"EmpN":7698},{"Ename":"ALLEN","Salary":1600,"EmpN":7499},
{"Ename":"WARD","Salary":1250,"EmpN":7521},{"Ename":"MARTIN","Salary":1250,"EmpN":7654},{"Ename":"TURNER","Salary":1500,
"EmpN":7844},{"Ename":"JAMES","Salary":950,"EmpN":7900}]}
------------------------------------------------------------------------------------------------------------------------
{"DeptNo":40,"EmployeeDetails":null}
------------------------------------------------------------------------------------------------------------------------

4 rows selected.

Note: maybe you could envisage to feedback in your topic, even if you have no new question, just to encourage us to continue to help you.

[Updated on: Fri, 03 February 2023 00:54]

Report message to a moderator

Re: Help need in writing query to get JSON format output [message #686925 is a reply to message #686924] Fri, 03 February 2023 00:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
Michel,

Thanks for testing it for me. I suppose one of these days I should install the latest version, so that I can experiment with all of the new features.

Barbara
Re: Help need in writing query to get JSON format output [message #686926 is a reply to message #686925] Fri, 03 February 2023 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In 21c, SQL*Plus offers a "set jsonprint pretty" command to pretty display JSON object.
Unfortunately it seems to not work in Windows, at least in my versions of Oracle and Windows (just like for XML strings).

But, as for XML with XMLSERIALIZE, you have the JSON_SERIALIZE function which constrains the result at SQL level:
MIKJ3DB1> select json_serialize(
  2         json_object(
  3           'DeptNo' : deptno,
  4           'EmployeeDetails' :
  5           (select json_arrayagg(json_object('Ename' : ename,
  6                                             'Salary' : sal,
  7                                             'EmpN' : empno))
  8            from   emp e
  9            where  e.deptno = d.deptno))
 10         pretty)
 11  from   dept d
 12  where deptno = 20
 13  /
JSON_SERIALIZE(JSON_OBJECT('DEPTNO':DEPTNO,'EMPLOYEEDETAILS':(SELECTJSON_ARRAYAGG(JSON_OBJECT('ENAME':ENAME,'SALARY':SAL
------------------------------------------------------------------------------------------------------------------------
{
  "DeptNo" : 20,
  "EmployeeDetails" :
  [
    {
      "Ename" : "JONES",
      "Salary" : 2975,
      "EmpN" : 7566
    },
    {
      "Ename" : "SCOTT",
      "Salary" : 3000,
      "EmpN" : 7788
    },
    {
      "Ename" : "FORD",
      "Salary" : 3000,
      "EmpN" : 7902
    },
    {
      "Ename" : "SMITH",
      "Salary" : 800,
      "EmpN" : 7369
    },
    {
      "Ename" : "ADAMS",
      "Salary" : 1100,
      "EmpN" : 7876
    }
  ]
}

1 row selected.
This function is also available (with many other useful options) in 19c.

Re: Help need in writing query to get JSON format output [message #686927 is a reply to message #686926] Fri, 03 February 2023 06:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel, you can get pretty json in 19C by using returning clause:

SELECT  JSON_OBJECT(
                    KEY 'EmployeeDetails'
                    VALUE JSON_ARRAYAGG(
                                        JSON_OBJECT(
                                                    KEY 'Ename'  IS ENAME,
                                                    KEY 'Salary' IS SAL,
                                                    KEY 'EmpN'   IS EMPNO
                                                    RETURNING VARCHAR2 PRETTY
                                                   )
                                        RETURNING VARCHAR2 PRETTY
                                       )
                    RETURNING VARCHAR2 PRETTY
                   ) JSON
  FROM  EMP
/
JSON
--------------------------------------------------------------------------------
{
  "EmployeeDetails" : [
  {
  "Ename" : "SMITH",
  "Salary" : 800,
  "EmpN" : 7369
},
  {
  "Ename" : "ALLEN",
  "Salary" : 1600,
  "EmpN" : 7499
},
  {
  "Ename" : "WARD",
  "Salary" : 1250,
  "EmpN" : 7521
},
  {
  "Ename" : "JONES",
  "Salary" : 2975,
  "EmpN" : 7566
},
  {
  "Ename" : "MARTIN",
  "Salary" : 1250,
  "EmpN" : 7654
},
  {
  "Ename" : "BLAKE",
  "Salary" : 2850,
  "EmpN" : 7698
},
  {
  "Ename" : "CLARK",
  "Salary" : 2450,
  "EmpN" : 7782
},
  {
  "Ename" : "SCOTT",
  "Salary" : 3000,
  "EmpN" : 7788
},
  {
  "Ename" : "KING",
  "Salary" : 5000,
  "EmpN" : 7839
},
  {
  "Ename" : "TURNER",
  "Salary" : 1500,
  "EmpN" : 7844
},
  {
  "Ename" : "ADAMS",
  "Salary" : 1100,
  "EmpN" : 7876
},
  {
  "Ename" : "JAMES",
  "Salary" : 950,
  "EmpN" : 7900
},
  {
  "Ename" : "FORD",
  "Salary" : 3000,
  "EmpN" : 7902
},
  {
  "Ename" : "MILLER",
  "Salary" : 1300,
  "EmpN" : 7934
}
]
}

SQL>
SY.
Re: Help need in writing query to get JSON format output [message #686930 is a reply to message #686927] Fri, 03 February 2023 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Interesting, you can then partially pretty print the JSON string (optimization between pretty and compact).
Using Barbara's previous query ("returning" clause is optional):
MIKJ3DB1> select json_object(
  2         'DeptNo' : deptno,
  3         'EmployeeDetails' :
  4         (select json_arrayagg(json_object('Ename' : ename,
  5                                           'Salary' : sal,
  6                                           'EmpN' : empno)
  7                               pretty)
  8          from   emp e
  9          where  e.deptno = d.deptno)
 10         pretty)
 11  from   dept d
 12  /
JSON_OBJECT('DEPTNO':DEPTNO,'EMPLOYEEDETAILS':(SELECTJSON_ARRAYAGG(JSON_OBJECT('ENAME':ENAME,'SALARY':SAL,'EMPN':EMPNO)R
------------------------------------------------------------------------------------------------------------------------
{
  "DeptNo" : 10,
  "EmployeeDetails" : [
  {"Ename":"KING","Salary":5000,"EmpN":7839},
  {"Ename":"CLARK","Salary":2450,"EmpN":7782},
  {"Ename":"MILLER","Salary":1300,"EmpN":7934}
]
}
------------------------------------------------------------------------------------------------------------------------
{
  "DeptNo" : 20,
  "EmployeeDetails" : [
  {"Ename":"JONES","Salary":2975,"EmpN":7566},
  {"Ename":"SCOTT","Salary":3000,"EmpN":7788},
  {"Ename":"FORD","Salary":3000,"EmpN":7902},
  {"Ename":"SMITH","Salary":800,"EmpN":7369},
  {"Ename":"ADAMS","Salary":1100,"EmpN":7876}
]
}
------------------------------------------------------------------------------------------------------------------------
{
  "DeptNo" : 30,
  "EmployeeDetails" : [
  {"Ename":"BLAKE","Salary":2850,"EmpN":7698},
  {"Ename":"ALLEN","Salary":1600,"EmpN":7499},
  {"Ename":"WARD","Salary":1250,"EmpN":7521},
  {"Ename":"MARTIN","Salary":1250,"EmpN":7654},
  {"Ename":"TURNER","Salary":1500,"EmpN":7844},
  {"Ename":"JAMES","Salary":950,"EmpN":7900}
]
}
------------------------------------------------------------------------------------------------------------------------
{
  "DeptNo" : 40,
  "EmployeeDetails" : null
}
------------------------------------------------------------------------------------------------------------------------

4 rows selected.

Note that this "pretty" option is not in the online SQL documentation.
Thanks

[Updated on: Fri, 03 February 2023 09:03]

Report message to a moderator

Re: Help need in writing query to get JSON format output [message #689411 is a reply to message #686930] Thu, 14 December 2023 06:51 Go to previous message
annabelle67
Messages: 1
Registered: December 2023
Junior Member
Thanks for the information!
Previous Topic: Created schedule based on date conditions
Next Topic: Problem in pivot query
Goto Forum:
  


Current Time: Thu May 23 09:44:28 CDT 2024