Update json value from sql column value

Question

Using SQL Server, I want to take column data and copy it into a json object column

I am using SQL Server to a query a column and a json data. What I want to do is to copy the data in column ename to fieldvalue column in the code below. If I could do it using SQL that would be great.

SELECT 
    a.id, a.ssn, a.ename, p.CaptionName, p.FieldName, p.FieldType, p.FieldValue
FROM 
    tablename as a
CROSS APPLY 
    OPENJSON (details)
        WITH (CaptionName NVARCHAR(100),
              FieldName NVARCHAR(100),
              FieldType NVARCHAR(15),
              FieldValue NVARCHAR(50)) AS P
WHERE
    p.captionname = 'txtEname' 
    AND a.ssn = '000-00-0000'

My json string in the details column

[{"CaptionName":"txtEname","FieldName":null,"FieldType":null,"FieldValue":""}]

I'm really not that good with sql which is what i want to use. after copying the data to the json object i will remove the ename column.

Answer 1

UPDATE 2019-07-11

Here's an amended solution which works for scenarios when there are multiple values in the JSON: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1fde45dfb604b2d5540c56f6c17a822d

update a
set details = JSON_MODIFY(details, '$[' + x.[key] + '].FieldValue', ename)
from dbo.tblUissAssignments a
CROSS APPLY OPENJSON (details, '$') x
CROSS APPLY OPENJSON (x.Value)
    WITH (CaptionName NVARCHAR(100),
        FieldName NVARCHAR(100),
        FieldType NVARCHAR(15),
        FieldValue NVARCHAR(50)) AS P
WHERE a.ssn = '000-00-0000'
and p.CaptionName = 'txtEname'    

This is similar to my original answer (see below). However:

• We now have 2 cross apply statements. The first is used to split the JSON array into elements, so we get a key (index) and value (JSON object as a string), as documented here: https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017#path
• The second does what your original CROSS APPLY did, only acting on the single array element.
• We use the [key] returned by the first cross apply to target the item in the array that we wish to update in our JSON_MODIFY statement.

NB: If it's possible for your JSON array to contain multiple objects that need updating, the best solution I can think of is to put the above statement into a loop; since 1 update will only update 1 index on a given JSON. Here's an example: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=120d2ac7dd3a024e5e503a5f64b0089e

declare @doWhileTrueFlag bit = 1

while (@doWhileTrueFlag = 1)
begin

    update a
    set details = JSON_MODIFY(details, '$[' + x.[key] + '].FieldValue', ename)
    from dbo.tblUissAssignments a
    CROSS APPLY OPENJSON (details, '$') x
    CROSS APPLY OPENJSON (x.Value)
        WITH (CaptionName NVARCHAR(100),
            FieldName NVARCHAR(100),
            FieldType NVARCHAR(15),
            FieldValue NVARCHAR(50)) AS P
    WHERE a.ssn = '000-00-0000'
    and p.CaptionName = 'txtEname'    
    and p.FieldValue != ename --if it's already got the correct value, don't update it again

    set @doWhileTrueFlag = case when @@RowCount > 0 then 1 else 0 end

end

---

ORIGINAL ANSWER

Try this: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b7b4d075cac6cd46239561ddb992ac90

update a
set details = JSON_MODIFY(details, '$[0].FieldValue', ename)
from dbo.tblUissAssignments a
cross apply
    OPENJSON (details)
        WITH (CaptionName NVARCHAR(100),
              FieldName NVARCHAR(100),
              FieldType NVARCHAR(15),
              FieldValue NVARCHAR(50)) AS P
where a.ssn = '000-00-0000'
and p.captionname = 'txtEname' 

More info on the JSON_MODIFY method here: https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?view=sql-server-2017

The subtle bit is that you're updating a json array containing a json object; not a single object. For that you have to include the index on the root element. See this post for some useful info on JsonPath if you're unfamiliar: https://support.smartbear.com/alertsite/docs/monitors/api/endpoint/jsonpath.html

---

Regarding scenarios where there's multiple items in the array, ideally we'd use a filter expression, such as this:

update a
set details = JSON_MODIFY(details, '$[?(@.CaptionName == ''txtEname'')].FieldValue', ename)
from dbo.tblUissAssignments a
where a.ssn = '000-00-0000'

Sadly MS SQL doesn't yet support these (see this excellent post: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016)

As such, I think we need to apply a nasty hack. Two such approaches spring to mind:

• Implement a loop to iterate through all matches
• Convert from JSON to some other type, then convert back to JSON afterwards

I'll think on these / whether there's something cleaner, since neither sits comfortably at present...