sql server query to json

 sql server query result to json object

      
	SELECT [SettingKey]
		   ,[SettingValue]
	FROM Config.ServiceSettings
	WHERE ServiceID =15
	FOR JSON PATH 
       
 

sql server JSON_MODIFY and OpenJson Tips

 Json Modify Mulitple json properties at the same time

       
declare @OrderBody nvarchar(max) = (select  sme.fnOrderBodyGet(@OrderID))
declare @OrderBodyNew nvarchar(max) 
set @OrderBodyNew =JSON_MODIFY(
		               JSON_MODIFY(JSON_MODIFY(@OrderBody, 'strict$.Details.TransactionNumber', cast(@paymentID as varchar))
								,'strict$.Details.PaymentReferenceNo',@PaymentReferenceNo )
					 ,'strict$.Details.PaymentMethodID',cast(@paymentMethodID as varchar))
       
 

Select Json Array as Table

       
 DECLARE @JSONINPUT nvarchar(4000) =
    '{
        "OUTER": [{
                    "ABC":"TEST_WB_New","XYZ":"9085"
                 },
                 {
                    "ABC":"TEST_WB1_New","XYZ":"9086"
                 },
                 {
                    "ABC":"TEST_WB2_New","XYZ":"9087"
                 }]
    }';

    SELECT ABC, XYZ 
    FROM OPENJSON(@JSONINPUT,'$.OUTER') 
    WITH (ABC NVARCHAR(50), XYZ NVARCHAR(50))
       
 

Select Single Property from json field

       
DECLARE @result nvarchar(max)
	declare @paymentResponse nvarchar(500)
select @paymentResponse = sme.fnPaymentResponseBodySTDGet(@PaymentID) 
	SELECT @result=PaymentProviderReferenceNo
FROM OPENJSON ( @paymentResponse )  
WITH (   
              PaymentProviderReferenceNo   VARCHAR(200)   '$.PaymentProviderReferenceNo' 
 )