时间戳

首页 > 解决方案 > 如何在选择语句中修改 json 列值以使用更新的 json 检索结果（表中没有更新值）

sql-server - 如何在选择语句中修改 json 列值以使用更新的 json 检索结果（表中没有更新值）

问题描述

我有两个 SQL Server 表FormSchema和AppRoles.

表格详细信息，输入详细信息请参见db fiddle

我正在尝试根据两个表中AppSchema的列在 select 语句中重构（在现有 json 值中创建或更新/附加）列值。SchemaId并且查询返回更新的结果而不在表中更新。

询问：

select 
    fs.SchemaId, fs.SchemaName,
    JSON_MODIFY(fs.AppSchema, '$.roles[1]', '{"role":"Test","permissions":{"create":true,"read":true,"update":false,"delete":true}}') as AppSchema
from 
    FormSchema fs
inner join   
    AppRoles ar on fs.SchemaId = ar.SchemaId

附加 json 将从AppRoles基于SchemaId.

预期结果：

SchemaId 模式名称 应用架构
1 车 {"roles":[{"role":"Support","permissions":{"create":false,"read":true,"update":false,"delete":false}},{"role" :"Test","permissions":{"create":true,"read":true,"update":false,"delete":true}}]}
2 吉普车 {"roles":[{"role":"CreateRead","permissions":{"create":true,"read":true,"update":false,"delete":false}}]}

如何通过 SQL 查询获得上述结果，并在 select 语句中按时修改 json？

提前致谢。

标签： sql-servertsql

解决方案

您可以使用APPLY子查询FOR JSON来获取构造的 Json 输出。

然后您可以使用JSON_MODIFYwithappend添加到数组中。您还需要使用JSON_QUERY来防止 Json 转义：

SELECT
    fs.SchemaId,
    fs.SchemaName,
    JSON_MODIFY(fs.AppSchema, 'append $.roles', JSON_QUERY(ar.newJson)) AS AppSchema
FROM FormSchema fs
CROSS APPLY (
  SELECT (
    SELECT Role AS [role],
        [create] AS [permissions.create],
        [read] AS [permissions.read],
        [update] AS [permissions.update],
        [delete] AS [permissions.delete]
    FROM AppRoles ar
    WHERE fs.SchemaId = ar.SchemaId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ) AS newJson
) ar;

如果要更改实际表中的现有值，可以将上面的内容转换为UPDATE

UPDATE fs
SET AppSchema =
    JSON_MODIFY(fs.AppSchema, 'append $.roles', JSON_QUERY(ar.newJson))
FROM FormSchema fs
CROSS APPLY (
  SELECT (
    SELECT Role AS [role],
        [create] AS [permissions.create],
        [read] AS [permissions.read],
        [update] AS [permissions.update],
        [delete] AS [permissions.delete]
    FROM AppRoles ar
    WHERE fs.SchemaId = ar.SchemaId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ) AS newJson
) ar;

推荐阅读