Using a sql query, how can I return an array of json objects that looks like this:

{
   "result":[
      {
         "RentBookRegistrationId":1,
         "date":"15-08-2022",
         "PersonName":"Peter",
         "Books":[
            {
               "name":"Ulysses"
            },
            {
               "name":"Hamlet
            }
         ],
         "Processes":[
            {
               "no":1,
               "name":"Online booking"
            },
            {
               "no":2,
               "name":"Reserved beforehand"
            },
            {
               "no":4,
               "name":"Vending machined used"
            }
         ]
      }
   ]
}

From a SQL Server database that looks like this:

Table: RentBookRegistration

 ---- ------------ ----------------------------- 
| id | date       | person                      |
 ---- ------------ ----------------------------- 
|  1 | 15-08-2022 | {"no": 1, "name": "Peter"}  |
 ---- -------- --- ----------------------------- 
|  2 | 16-08-2022 | {"no": 2, "name": "Anna"}   |
 ---- ------------ ----------------------------- 
|  3 | 17-08-2022 | {"no": 1, "name": "Peter"}  |
 ---- ------------ ----------------------------- 
|  4 | 17-08-2022 | {"no": 2, "name": "Mark"}   |
 ---- ------------ ----------------------------- 

Table: BookData

 ---- ------------------------ ----------------------------------------------------------- 
| id | rentBookRegistrationId | book                                                      |
 ---- ------------------------ ----------------------------------------------------------- 
|  1 | 1                      | {"name": "Ulysses", "author": "James Joyce", "year": 1918}| 
 ---- ------------------------ ----------------------------------------------------------- 
|  2 | 1                      | {"name": "Hamlet", "author": "Shakespeare", "year": 1601} |  
 ---- ------------------------ ----------------------------------------------------------- 
|  3 | 2                      | {"name": "Dune", "author": "Frank Herbert", "year": 1965} |
 ---- ------------------------ ----------------------------------------------------------- 
|  4 | 3                      | {"name": "Hamlet", "author": "Shakespeare", "year": 1601} |  
 ---- ------------------------ ----------------------------------------------------------- 
|  5 | 4                      | {"name": "Hamlet", "author": "Shakespeare", "year": 1601} |  
 ---- ------------------------ ----------------------------------------------------------- 

Table: ProcessData

 ---- ------------------------ ----------------------------------------------------------- 
| id | rentBookRegistrationId | processUsed                                               |
 ---- ------------------------ ----------------------------------------------------------- 
|  1 | 1                      | {"no": 1, "name": "Online booking"}                       | 
 ---- ------------------------ ----------------------------------------------------------- 
|  2 | 1                      | {"no": 2, "name": "Reserved beforehand"}                  |  
 ---- ------------------------ ----------------------------------------------------------- 
|  3 | 1                      | {"no": 4, "name": "Vending machined used"}                |  
 ---- ------------------------ ----------------------------------------------------------- 
|  3 | 2                      | {"no": 1, "name": "Online booking"}                       | 
 ---- ------------------------ ----------------------------------------------------------- 
|  4 | 2                      | {"no": 4, "name": "Vending machined used"}                |  
 ---- ------------------------ ----------------------------------------------------------- 
|  5 | 3                      | {"no": 2, "name": "Reserved beforehand"}                  |  
 ---- ------------------------ ----------------------------------------------------------- 

The table layout might seems a bit stupid, but they are simplified to make the question straightforward.

This is how far I've come so far:

select … from RentBookRegistration R where R.PersonName = 'Peter'

CodePudding user response：

If you can't fix the table structure, you can use JSON_VALUE and JSON_QUERY to do what you want to do. But I recommend fixing the table structures. Better performance, better resource use, and much simpler queries that are more human readable.

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16

CodePudding user response：

This isn't pretty. You are storing JSON data and then want to consume that JSOn data and turn it into different JSON data. Honestly, you would be better off storing your data in a normalised format, and then you can make the JSON more easily.

Saying that, you can do this, but you need to consume your JSON data first, with OPENJSON and then turn it back into JSON with FOR JSON:

SELECT (SELECT RBR.id AS RentBookRegistrationId,
               RBR.date,
               p.name,
               (SELECT b.name
                FROM dbo.BookData BD
                     CROSS APPLY OPENJSON(BD.book)
                                 WITH (name nvarchar(50)) b
                WHERE BD.rentBookRegistrationId = RBR.id
                FOR JSON AUTO) AS Books,
               (SELECT pU.no,
                       pU.name
                FROM dbo.ProcessData PD
                     CROSS APPLY OPENJSON(PD.processUsed)
                                 WITH (no int,
                                       name nvarchar(50)) pU
                WHERE PD.rentBookRegistrationId = RBR.id
                FOR JSON AUTO) AS Processes
        FROM OPENJSON(RBR.person)
             WITH (no int,
                   name nvarchar(50)) p
        FOR JSON PATH,ROOT('result'))
FROM dbo.RentBookRegistration RBR;

I assume you want 1 row per person.

db<>fiddle