CODEWITHSUNDEEP
jsonsql-server
Krzysztof Krysztofczyk
Krzysztof Krysztofczyk

Reputation: 499

SQL Server : creating Json column

I would like to create a column that contains JSON with fields calculated based on another table columns. Of course, I can make that as below but that works fine only if the case is quite simple, and still, it is easy to make mistake. Is it a more convenient way to build JSON inside of query?

SELECT 
    1 AS id, 'AAA' AS ProductCode, 
    'Camera' AS Product, 5000 AS Price 
INTO
    #tmp
UNION
SELECT 
    2 AS id, 'BBB' AS ProductCode, 
    'Tripod ' AS Product, 80 AS Price
UNION
SELECT 
    3 AS id, 'CCC' AS ProductCode, 
    'Objective ' AS Product, 7000 AS Price

 
SELECT 
    ProductCode,
    '{' +  '"Product":"' + Product + '"' + ', '
        +  '"Price":"' + cast (Price as nvarchar) 
        +  '"}' AS Details     
FROM  
    #tmp

Upvotes: 1

Views: 484

Answers (1)

Zhorov
Zhorov

Reputation: 30013

You may use FOR JSON, but you need at least SQL Server 2016:

SELECT 
   ProductCode,
   (SELECT Product, Price FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS Details      
FROM #tmp

Starting from SQL Server 2022, you may use JSON_OBJECT():

SELECT 
   ProductCode,
   JSON_OBJECT('Product': Product, 'Price': Price) AS Details      
FROM #tmp

Upvotes: 4

Related Questions