Click here to Skip to main content
14,869,463 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 1-Mar-21 3:19am

1 solution

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[^]
   
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.

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