Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I'm getting the below data from SQL view.

+-------+-------+------------+------------+---------+-----------+----------------+
| ID    | Name  | Desc       | Relation   | ChildId | ChildName | ChildDesc      |
+-------+-------+------------+------------+---------+-----------+----------------+
| 10111 | Motor | Main Motor | Accessory  | 30123   | Bolt      | Hexagonal Bolt |
| 10111 | Motor | Main Motor | Accessory  | 30124   | Nut       | 25mm Dia       |
| 10111 | Motor | Main Motor | Spare      | 30125   | screw     | Type A         |
| 10111 | Motor | Main Motor | Spare      | 30126   | Shaft     | 10m long       |
| 10112 | Engine| 800cc      | Spare      | 30127   | Oil       | Standard oil   |
+-------+-------+------------+------------+---------+-----------+----------------+


Now i have to provide the below response when user hits the http://localhost:8080/items?id=10111

{
	"Id": "10111",
	"Name": "Motor",
	"Desc": "Main Motor",
	"Accessory": [
		{
			"Id": "30123",
			"Name": "Bolt",
			"Desc": "Hexagonal Bolt"
		},
		{
			"Id": "30124",
			"Name": "Nut",
			"Desc": "25mm Dia"
		},
	],
	"Spare": [
		{
			"Id": "30125",
			"Name": "screw",
			"Desc": "Type A"
		},
		{
			"Id": "30126",
			"Name": "Shaft",
			"Desc": "10m long"
		},
	]
}<pre>


Similarly when user hits the http://localhost:8080/items?id=10112

{
	"Id": "10112",
	"Name": "Engine",
	"Desc": "800cc",
	"Accessory": [],
	"Spare": [
		{
			"Id": "30127",
			"Name": "Oil",
			"Desc": "Standard oil"
		}
	]
}


There will be only one id per request. Please help to achieve this.

What I have tried:

I have tried below

Repository :

@Repository
public interface MyDataRepo extends JpaRepository<Items, String> {

    @Query(value="select ID,Name,Desc,Relation,ChildId,ChildName,ChildDesc from myview
                  WHERE ID=?1",nativeQuery=true)
    List<Data> findAllCategory(String id);

    public static interface Data {
      String getid();
      String getname();
      String getdesc();
      String getrelation();
      String getchildid();
      String getchildname();
      String getchilddesc();
    }
}


Service:

public List<Data> getMyData(String id) {
    return repo.findAllCategory(id);
}


Controller :

@GetMapping("/items")
public ResponseEntity<List<Data>> retrieveData(@RequestParam("id") String id) {
    List<Data> stud = service.getMyData(id);
    return ResponseEntity.ok().body(stud);
}


Current ouput:

[{
	"Id": "10111",
	"Name": "Motor",
	"Desc": "Main Motor",
	"Relation":"Accessory",
	"ChildId":"30123",
	"ChildName":"Bolt",
    "ChildDesc":"Hexagonal Bolt"
	
}, {
	"Id": "10111",
	"Name": "Motor",
	"Desc": "Main Motor",
	"Relation":"Accessory",
	"ChildId":"30124",
	"ChildName":"Nut",
    "ChildDesc":"25mm Dia"
}, {
	"Id": "10111",
	"Name": "Motor",
	"Desc": "Main Motor",
	"Relation":"Spare",
	"ChildId":"30125",
	"ChildName":"screw",
    "ChildDesc":"10m long "
}, {
	"Id": "10111",
	"Name": "Motor",
	"Desc": "Main Motor",
	"Relation":"Spare",
	"ChildId":"30126",
	"ChildName":"Shaft",
    "ChildDesc":"Pasted Seal"
}]
Posted
Updated 24-Nov-22 12:27pm

I googled it and this was the first result returned:

Format Nested JSON Output with PATH Mode - SQL Server | Microsoft Docs[^]


My search results are here:

sql server nested json at DuckDuckGo[^]
 
Share this answer
 
Comments
Maciej Los 1-Mar-21 9:31am    
5ed!
Member 15065455 1-Mar-21 10:05am    
Thanks for the response. Actually I'm looking for Java spring boot solution on how to configure the output to create the nested response.
#realJSOP 1-Mar-21 10:55am    
Your question wasn't framed that way. They best way to do it is IN the sql query (assuming you're using a real SQL implementation). At that point, it doesn't matter what your using to process it because it will come back as JSON, and JSON parsing is ubiquitous in pretty much every language in popular use today.
Were you able to solve this problem? If yes kindly let me know how.
 
Share this answer
 
Comments
Richard Deeming 25-Nov-22 5:19am    
If you want to ask for more information, click the "Have a Question or Comment?" button under the question and post a comment. Do not post your comment as a "solution" to the question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900