How to parse JSON NVarChar(max) in SQL Server for Update

Question

I am trying to both

1. Save a JSON string to a table cell
2. Parse part of the JSON string and set it to its own cell

Using SQL Server

 create procedure json_parse_and_update
   @i int,
   @json nvarchar(max)
 as 

update testTable
set 
json_str = @json, -- Save entire JSON string to table cell
json_prop1 = json_value(my_prop,'$.nested.property'), -- Parse JSON and update a single column 
json_prop2 = json_value(my_prop2,'$.nested2.property2')
from @json
where [key] = @i;

However, I'm not sure how to select a JSON property from a variable, the examples I see are selecting JSON properties from a table.

declare @json nvarchar(max) = '{
  "nested": {
    "property1": "value",
    "property2": 0
  },
  "nested2": {
    "property1": "value",
    "property2": 0
  }
}'

Test table is like

json_str | json_prop1       | json_prop_2       |
---------+------------------+-------------------+
json_str | nested_property1 | nested2_property2 |

How would I select and update nested.property into a column named json_prop1?

Answer 1

What is my_prop? Try to do like this:

json_prop1 = json_value(@json,'$.nested.property')
json_prop2 = json_value(@json,'$.nested2.property2')