Correct json formatting in ms sql server

I have the following SQL table

user_id  product_1  product_2  product_3
123      Scissors   Stone      Paper

I wrote this code to format result as JSON, but can’t get it in the desired format. If i name all product columns as name then sql returns an error Use different names and aliases for each column in SELECT list.

Sql code:

select 
    product1 as 'Product1', 
    product2 as 'Product2', 
    product3 as 'Product3'
from Recommendations
where user_id = '123'
FOR JSON PATH, ROOT('Offers')

Current output:

{"offers":[
   {"Product1": "Scissors", "Product2": "Stone", "Product3": "Paper"}
  ]
}

Desired output:

{"offers":[
   {"name": "Scissors"}, 
   {"name": "Stone"}, 
   {"name": "Paper"}
  ]
} 

Answer

You may try with the next approach:

Table:

CREATE TABLE Recommendations (
   user_id int,  
   product1 varchar(100),  
   product2 varchar(100),    
   product3 varchar(100)
)   
INSERT INTO Recommendations
   (user_id, product1, product2, product3)
VALUES   
   (123, 'Scissors', 'Stone', 'Paper')

Statement:

SELECT v.Name
FROM Recommendations r
CROSS APPLY (VALUES (r.product1), (r.product2), (r.product3)) v(Name)
WHERE user_id = '123'
FOR JSON PATH, ROOT('Offers')

Result:

{"Offers":[{"Name":"Scissors"},{"Name":"Stone"},{"Name":"Paper"}]}