SQL Server : creating Json column

Question

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

Zhorov · Accepted Answer · 2023-05-05 11:31:29Z

4

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

edited May 5, 2023 at 11:31

answered Aug 3, 2021 at 17:42

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

1 Comment

lifedroid Over a year ago

This is a fantastic method for creating a query that returns a mix of standard columns and JSON columns.

Collectives™ on Stack Overflow

SQL Server : creating Json column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related