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  |
 |
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 #686910 is a reply to message #686905] |
Tue, 31 January 2023 01:06  |
 |
Michel Cadot
Messages: 68418
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 |
|
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 #686924 is a reply to message #686922] |
Fri, 03 February 2023 00:52 |
|
Michel Cadot
Messages: 68418
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 #686926 is a reply to message #686925] |
Fri, 03 February 2023 02:19 |
|
Michel Cadot
Messages: 68418
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.
|
|
|
|
| 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 |
Solomon Yakobson
Messages: 3212
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>
|
|
|
|
| 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 |
|
Michel Cadot
Messages: 68418
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 |
|
|
|
Goto Forum:
Current Time: Sun Mar 26 19:23:54 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
