英文:
Update value from json dictionary by key in MSSQL
问题
I'm here to help with the Chinese translation. However, it seems like your message consists mostly of code, which may not translate well. Could you please provide a brief summary or explanation of what you're trying to achieve in English, and I'll be happy to assist you with the translation?
英文:
I need to update a dictionary value in a field in a table that contains json in Microsoft SQL.
So if the table and data is this:
CREATE TABLE Visits
(
[Id] UNIQUEIDENTIFIER NOT NULL,
[AdditionalInfo] NVARCHAR(4000) NULL
)
insert into visits
(id,AdditionalInfo)
values
(newid(),'{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z"}}')
insert into visits
(id,AdditionalInfo)
values
(newid(),'{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z","7f90a25b-f5d4-eb11-bacb-0022481aa333":"2022-10-11T14:42:00Z"}}')
I want to update the time of the last visit, given an arbitary key.
For example, where the key is '7f90a25b-f5d4-eb11-bacb-0022481aa333', I want it now to be '2023-07-06T13:00:00Z'
So the second row with the AdditionalInfo field that was:
{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z","7f90a25b-f5d4-eb11-bacb-0022481aa333":"2022-10-11T14:42:00Z"}}
should now be:
{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z","7f90a25b-f5d4-eb11-bacb-0022481aa333":"2023-07-06T13:00:00Z"}}
and the first row is left as it was.
Any help would be apprieciated.
I did get as far as to be able to select the row:
select id, additionalinfo from visits
where exists(select [key] , [value] from openjson(additionalinfo, '$.LastVisit') where [key] = '7f90a25b-f5d4-eb11-bacb-0022481aa333')
But I got stuck getting any further with updating the value. I did look into json_modify, but that needs a jsonpath, and I can't find the syntax for a dictionary (key/value, where the keys are all different).
答案1
得分: 0
你可以尝试像这样做：
```sql
with data as (
select '{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z"}}' as json
union select '{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z","7f90a25b-f5d4-eb11-bacb-0022481aa333":"2022-10-11T14:42:00Z"}}'
)
select *
, json_modify(json, '$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"', '2023-01-01T12:12:00')
from data
where json_value(json, '$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"') is not null
WHERE 确保你获取正确的 JSON，并且 json_modify 修改你所寻找的节点。
<details>
<summary>英文:</summary>
You can do something like this i guess:
```sql
with data as (
select '{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z"}}' as json
union select '{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z","7f90a25b-f5d4-eb11-bacb-0022481aa333":"2022-10-11T14:42:00Z"}}'
)
select *
, json_modify(json, '$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"', '2023-01-01T12:12:00')
from data
where json_value(json, '$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"') is not null
WHERE makes sure you get correct json, and the json_modify modifies the node you're looking for
答案2
得分: 0
根据您的JSON内容结构不同，实际方法会有所不同，但是对于没有嵌套JSON数组的JSON对象，可以使用JSON_MODIFY()语句：
UPDATE Visits
SET [AdditionalInfo] = JSON_MODIFY(
[AdditionalInfo],
'$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"',
'2023-07-06T13:00:00Z'
)
WHERE JSON_VALUE(
[AdditionalInfo],
'$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"'
) IS NOT NULL
从SQL Server 2022开始，您可以在WHERE子句中使用JSON_PATH_EXISTS()：
...
WHERE JSON_PATH_EXISTS(
[AdditionalInfo],
'$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"'
) = 1
英文:
The actual approach depends on the structure of your JSON content, but in case of JSON object(s) without nested JSON arrays, a statement using JSON_MODIFY() is an option:
UPDATE Visits
SET [AdditionalInfo] = JSON_MODIFY(
[AdditionalInfo],
'$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"',
'2023-07-06T13:00:00Z'
)
WHERE JSON_VALUE(
[AdditionalInfo],
'$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"'
) IS NOT NULL
Starting from SQL Server 2022, you may use JSON_PATH_EXISTS() in the WHERE clause:
...
WHERE JSON_PATH_EXISTS(
[AdditionalInfo],
'$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"'
) = 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论