Correct json formatting in ms sql server

Question

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"}
  ]
}

Zhorov · Accepted Answer · 2019-10-01 11:35:46Z

5

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"}]}

answered Oct 1, 2019 at 11:35

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Correct json formatting in ms sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related