Skip to content
Advertisement

How to get data from json column in SQL Server that starts with array element

I have a database table that I need to extract data from where the column of interest has json it. What makes this particularly difficult is the most outer elements of the json is ‘[‘ & ‘]’ as in the parent element is an array. I need to get the value associated with key ‘Name’ (which in this case is ‘MS220’). However, I’m not able to path correctly to the key I want.

The below JData table is a duplicate copy of the data I need to perform the extract on. Between SELECT OPENJSON, JSON_VALUE, JSON_QUERY etc., how can I retrieve the value I’m looking for?

Below is a couple of selects I’ve tried but not quite getting it.

@AaronBertrand: I had to modify the answer a little since the table also has a column labeled [name] as well. Is there a way to UPDATE ParamName to a new value?

Advertisement

Answer

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement