How to add json object using JSON_MODIFY?

Vladimir Moldovanenko 276 Reputation points
2022-07-12T21:52:24.19+00:00

Hi everyone.

Thanks for looking at my issue.

DECLARE @j nvarchar(max) =  
'{  
  "version": "1.0",  
  "content": [  
    {},  
    {  
      "dataField": "Queue",  
      "name": "Queue",  
      "editorType": "dxTextBox",  
      "visibleIndex": 2,  
      "colSpan": 2,  
      "editorOptions": {  
        "placeholder": "MSMQ Queue"  
      },  
      "groupItemOptions": {  
        "name": "output"  
      }  
    }  
  ]  
}'  
  
DECLARE @key_content int = 1  
  
DECLARE @placeholder nvarchar(max) = JSON_VALUE(@j, CONCAT('lax $.content', QUOTENAME(@key_content), '.editorOptions.placeholder'))  
  
SET @placeholder = CONCAT('{"text":"', @placeholder, '"}')  
  
SET @j = JSON_MODIFY  
(  
    @j  
    ,CONCAT('append $.content', QUOTENAME(@key_content), '.label')  
    ,JSON_QUERY(@placeholder)  
)  
  
SELECT @j as [@j], JSON_VALUE(@j, CONCAT('lax $.content', QUOTENAME(@key_content), '.label.text')) as placeholder  

I am getting an array

,"label":[{"text":"MSMQ Queue"}]}  

but want an object

,"label":{"text":"MSMQ Queue"}}  

Why JSON_QUERY adds []?

What am I missing here?

Thanks
Vladimir

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,689 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 26,376 Reputation points
    2022-07-13T00:17:32.43+00:00

    Hi @Vladimir Moldovanenko ,

    Please try the following:

    SET @j = JSON_MODIFY  
     (  
         @j  
         ,CONCAT('$.content', QUOTENAME(@key_content), '.label')  
         ,JSON_QUERY(@placeholder)  
     );  
    

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.