由于 In 运算符提供的值，SQL 查询在 JSON_MODIFY 中选择不同的行结果 [英] SQL query selecting different row result in JSON_MODIFY because of In operator provided value

问题描述

我有两个 sql 表名称为commonformsschema 列是 SchemaId,SchemaName,Tenant,Schema

i have two sql tables name as commonformsschema columns are SchemaId,SchemaName,Tenant,Schema

和 ApplicationRoles 列是 SchemaId,RoleName,create,read,update,delete.

事情就像一个 SchemaId 可以存在于多个 RoleName 中.因此，当我尝试在 JSON_Modify 中执行以下查询时，不同的 roleName 结果被追加.我已经尝试过但没有找到任何解决方法.

And things is like One SchemaId can we exists in Multiple RoleName. So when i try to execute the following query in JSON_Modify different roleName result getting appended.i have tried but not found anything how to solve this.

这里是查询:

select *
from
(
    select fs1.SchemaId,
     ----------------------------------------------not working as expected
           JSON_MODIFY(
                          fs1.[Schema],
                          ISNULL(
                                    N'$.roles[' +
                                    (
                                        SELECT j.[key]
                                        FROM OPENJSON(fs1.[Schema], '$.roles') j
                                        WHERE JSON_VALUE(j.value, '$.role') = ar1.rolename
                                    ) COLLATE Latin1_General_BIN2 + N']',
                                    N'append $.roles'
                                ),
                          JSON_QUERY(
                          (
                              SELECT ar1.RoleName AS [role],
                                     ar1.[create] AS [permissions.create],
                                     ar1.[read] AS [permissions.read],
                                     ar1.[update] AS [permissions.update],
                                     ar1.[delete] AS [permissions.delete]
                              FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                          )
                                    )
                      ) AS [Schema],
-----------------------------------------------------------------
           
           row_number() over (partition by fs1.schemaName
                              order by case
                                           when fs1.tenant = 'ALL' then
                                               2
                                           else
                                               1
                                       end,
                                       fs1.tenant
                             ) as seqnum
    from ApplicationRoles ar1
        join commonformsschema fs1
            on ar1.SchemaId = fs1.SchemaId
    where fs1.Tenant in ( 'constructiontest', 'All' )
          and ar1.RoleName in ( 'Construction Manager Admin', 'Project Manager Admin', 'Read' )
) t3
where seqnum = 1

有关详细信息，请参阅db fiddler

当前结果:只有单个角色名称对象 Construction Manager Admin 获取追加而不更新架构列角色属性中现有的 Read 角色名称对象.

Current Result: only single rolename object Construction Manager Admin getting append and not updating existing Read rolename object in schema column roles property.

{
  "roles": [
    {
      "role": "Default",
      "permissions": {
        "read": false,
        "create": false,
        "update": false,
        "delete": false
      }
    },
    {
      "role": "Read",
      "permissions": {
        "read": false,
        "create": false,
        "update": false,
        "delete": false
      }
    },
    {
      "role": "Construction Manager Admin",
      "permissions": {
        "create": false,
        "read": false,
        "update": false,
        "delete": false
      }
    }
  ]
}

预期结果:角色名称 Construction Manager Admin、Project Manager Admin add 和 Read 角色名称对象都应在架构列角色属性中更新.

Expected Result: Both rolename Construction Manager Admin,Project Manager Admin add and Read rolename object should be updated in schema column roles property.

{
  "roles": [
    {
      "role": "Default",
      "permissions": {
        "read": false,
        "create": false,
        "update": false,
        "delete": false
      }
    },
    {
      "role": "Read",
      "permissions": {
        "read": true,
        "create": false,
        "update": false,
        "delete": false
      }
    },
    {
      "role": "Construction Manager Admin",
      "permissions": {
        "create": false,
        "read": false,
        "update": false,
        "delete": false
      }
    },
    {
      "role": "Project Manager Admin",
      "permissions": {
        "create": true,
        "read": true,
        "update": true,
        "delete": true
      }
    }
  ]
}

请帮忙.

提前致谢.

推荐答案

您不能期望 JSON_MODIFY 使用不同的行递归运行多次.每次执行将每行独立运行一次，因此每个对象都分别应用于原始 JSON.

You cannot expect JSON_MODIFY to run multiple times recursively using different rows. Each execution will run once independently per row, so each object is being applied to the original JSON separately.

如果你删除 where seqnum = 1 你就会明白我的意思.

If you remove where seqnum = 1 you will see what I mean.

相反，我认为您应该从头开始重建整个 JSON 对象.

由于您不想从 JSON 中删除任何不在现有行中的内容，这变得更加复杂

This is made more complicated by the fact that you don't want to delete anything from the JSON that isn't in your existing rows

• 我们首先将 commonformsschema 过滤为您想要的行，同时考虑 All.
• 然后，在 APPLY 中，我们从 ApplicationRoles....
获取我们需要的行
• ...并将现有的 JSON 破解成单独的行
• ... 和 FULL JOIN 它们在一起(我们需要 FULL 因为 Default 在表中不存在)
• 我们使用显式路径语法和根 roles 对象构建 JSON.
• 我已经放入了一个 ORDER BY 以保持原始 JSON 的顺序，但如果您不在乎，则没有必要这样做.
• APPLY 子查询也可以直接在 SELECT 中作为相关子查询完成，就像您所做的那样.

• We begin by filtering commonformsschema to only the rows you want, taking All into account.
• Then, within an APPLY, we take the rows we need from ApplicationRoles....
• ... and crack open the existing JSON into separate rows
• ... and FULL JOIN them together (we need FULL because Default doesn't exist in the table)
• We construct JSON out of that using explicit path syntax, and a root roles object.
• I've put in an ORDER BY to keep the ordering of the original JSON, but that is not necessary if you don't care.
• The APPLY subquery can also be done as a correlated subquery directly in the SELECT, as you have done.

SELECT
    fs1.SchemaId,
    j.newJson
FROM (
    SELECT *,
        row_number() over (partition by fs1.schemaName
                   order by case when fs1.tenant = 'ALL'
                              then 2 else 1 end,
                            fs1.tenant
                             ) as seqnum
    FROM commonformsschema fs1
    WHERE fs1.Tenant IN ( 'constructiontest', 'All' )
) fs1
CROSS APPLY (
    SELECT
        ISNULL(ar1.rolename, j2.role) role,
        ISNULL(ar1.[create], j2.[create]) [permissions.create],
        ISNULL(ar1.[read], j2.[read]) [permissions.read],
        ISNULL(ar1.[update], j2.[update]) [permissions.update],
        ISNULL(ar1.[delete], j2.[delete]) [permissions.delete]
    FROM (
        SELECT *
        FROM ApplicationRoles ar1
        WHERE ar1.SchemaId = fs1.SchemaId
          AND ar1.RoleName IN (
            'Construction Manager Admin',
            'Project Manager Admin',
            'Read' )
    ) ar1
    FULL JOIN OPENJSON (fs1.[Schema], '$.roles') j1
        CROSS APPLY OPENJSON (j1.value)
            WITH (
              [role] nvarchar(100),
              [create] bit '$.permissions.create',
              [read] bit '$.permissions.read',
              [update] bit '$.permissions.update',
              [delete] bit '$.permissions.delete'
            ) j2
      ON j2.[role] = ar1.rolename
    WHERE (fs1.ModifiedDateTime > '2021-07-12 04:25:57.0000000' OR
           ar1.ModifiedDateTime > '2021-07-12 04:25:57.0000000')
    ORDER BY ISNULL(j1.[key], '9999')
    FOR JSON PATH, INCLUDE_NULL_VALUES, ROOT ('roles')
) j(newJson)
WHERE fs1.seqnum = 1
  AND j.newJson IS NOT NULL;

db<>fiddle.uk

这篇关于由于 In 运算符提供的值，SQL 查询在 JSON_MODIFY 中选择不同的行结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！