Append json value

Question

Append json value

farax_x

Hall of Fame

Points: 3579
More actions
April 29, 2017 at 4:39 am

#324907

Hi all,
I have json like this
DECLARE @j NVARCHAR(max) = N'[{"Views":1,"Date":1493119613,"MediaCaption":"test"}]'
i want to add "size"="1000" to this array
SET @j = JSON_MODIFY(@j,'append $[0].size' , 1000) SELECT @j
but It append "size":[1000]
how can Id add "size"="1000" instead of "size":[1000] ?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

farax_x - Saturday, April 29, 2017 4:39 AM
Hi all,
I have json like this
DECLARE @j NVARCHAR(max) = N'[{"Views":1,"Date":1493119613,"MediaCaption":"test"}]'
i want to add "size"="1000" to this array
SET @j = JSON_MODIFY(@j,'append $[0].size' , 1000) SELECT @j
but It append "size":[1000]
how can Id add "size"="1000" instead of "size":[1000] ?

Interesting, not by a 2016 box right now but have you tried skipping the enumeration?
😎
SET @j = JSON_MODIFY(@j,'append $.size' , 1000) SELECT @j

farax_x Hall of Fame Points: 3579 More actions · Answer 2

Eirikur Eiriksson - Saturday, April 29, 2017 7:06 AM
farax_x - Saturday, April 29, 2017 4:39 AM
Hi all,
I have json like this
DECLARE @j NVARCHAR(max) = N'[{"Views":1,"Date":1493119613,"MediaCaption":"test"}]'
i want to add "size"="1000" to this array
SET @j = JSON_MODIFY(@j,'append $[0].size' , 1000) SELECT @j
but It append "size":[1000]
how can Id add "size"="1000" instead of "size":[1000] ?
Interesting, not by a 2016 box right now but have you tried skipping the enumeration?
😎
SET @j = JSON_MODIFY(@j,'append $.size' , 1000) SELECT @j

Yes, but I doesn't work !

debasis.yours Hall of Fame Points: 3568 More actions · Answer 3

Hi,

Have you tried enclosing the value in single-quotes?

I mean JSON_MODIFY(@j, 'append $.size', '1000')

Please let us know.

farax_x Hall of Fame Points: 3579 More actions · Answer 4

debasis.yours - Saturday, April 29, 2017 10:27 AM
Hi,
Have you tried enclosing the value in single-quotes?
I mean JSON_MODIFY(@j, 'append $.size', '1000')
Please let us know.

My problem is about putting array instead of single value

"size"="1000" ==>ok
"size":["1000"] ==>not ok

Jovan Popovic SSC Enthusiast Points: 191 More actions · Answer 5

Maybe without append:

DECLARE @j-2 NVARCHAR(max) = N'[{"Views":1,"Date":1493119613,"MediaCaption":"test"}]'

SET @j-2 = JSON_MODIFY( @j-2,'$[0].size' , 1000)

SELECT @j-2