Simple Question : How to retract value from JSON data??

Question

Hi there,

I have a JSON sample data as the shown below:

{"i:0#.w|my\jeffw":{"DateTime":"/Date(1604906464292)/", "LoginName":"SHAREPOINT\system"},
"i:0#.w|my\yex":{"DateTime":"/Date(1604909155060)/", "LoginName":"SHAREPOINT\system"},
"i:0#.w|my\danj":{"DateTime":"/Date(1604914548113)/", "LoginName":"my\jeffw"}}

How should I extract the "/Date(1604914548113)/" from DateTime??

Thanks

Answer 1

To get the value as a string, check this example:

declare @json varchar(max) = '  
{"i:0#.w|my\\jeffw":{"DateTime":"\/Date(1604906464292)\/", "LoginName":"SHAREPOINT\\system"},  
"i:0#.w|my\\yex":{"DateTime":"\/Date(1604909155060)\/", "LoginName":"SHAREPOINT\\system"},  
"i:0#.w|my\\danj":{"DateTime":"\/Date(1604914548113)\/", "LoginName":"my\\jeffw"}}  
'  
  
declare @result varchar(max)  
  
set @result = json_value(@json, '$."i:0#.w|my\\danj"."DateTime"')  
  
select @result  

You can also access it by LoginName, if it is unique. It depends on how you identify the data.

A similar approach can be used with database columns.

(It is possible to convert this result to datetime, which is probably a different question).