TSQL Azure Modify JSON in array

Question

I'm using SQL azure and storing JSON data in a varchar column. Can anyone advise how I can update a value stored in array? I would like to change status from Active to Disabled for example.

JSON Sample

{
  "services": [
    {
      "attributes": {
        "Status": "Active",
        "Additional_Notes": ""
      },
      "type": "Type1",
      "description": "a",
      "reference": "312ce8e7-9913-4758-82af-10551d63920a"
    },
    {
      "attributes": {
        "ContractNo": "1234",
        "Additional_Notes": ""
      },
      "type": "Type2",
      "description": "b",
      "reference": "962c7bc6-882c-47ee-b581-c5d3436d4f99"
    },
    {
      "attributes": {
        "ContractNo": "5678",
        "Additional_Notes": "test note 123"
      },
      "type": "Type3",
      "description": "b",
      "reference": "86fc37ed-59d3-42c4-a0be-ca54bfdc0fec"
    }
  ]
}

I currently use JSON_VALUE and JSON_QUERY to query the data which is fine, but not idea how to use JSON_MODIFY to edit the value. I know the reference but not sure how to get to the value.

Any advice would be much appreciated.

Ps. I'm stuck with the JSON in this format and cannot change it.

TheGameiswar · Accepted Answer · 2018-01-08T13:30:20.957

You need to use JSON_Modify to change JSON, for the given question, you can do like below

declare @json varchar(1000)='{
  "services": [

    {
      "attributes": {
        "Status": "Active",
        "Additional_Notes": ""
      },
      "type": "Type1",
      "description": "a",
      "reference": "312ce8e7-9913-4758-82af-10551d63920a"
    },

    {
      "attributes": {
        "ContractNo": "1234",
        "Additional_Notes": ""
      },
      "type": "Type2",
      "description": "b",
      "reference": "962c7bc6-882c-47ee-b581-c5d3436d4f99"
    },
    {
      "attributes": {
        "ContractNo": "5678",
        "Additional_Notes": "test note 123"
      },
      "type": "Type3",
      "description": "b",
      "reference": "86fc37ed-59d3-42c4-a0be-ca54bfdc0fec"
    }
  ]
}'

set @json= JSON_modify(@json,'$.services[0].attributes.Status','Any new value')

select @json

You will need to first understand what are the JSON functions available and what they can do.For this ,this link helped me:The Ultimate SQL Server JSON Cheat Sheet

Some more references to update multiple elements: SQL Server JSON_Modify, How to Update all?

Thanks this is what I needed. I just first do a query to get the index. — David Hawkins, Jan 09 '18 at 16:31

TSQL Azure Modify JSON in array

1 Answers1