主页 / coding / 问题 / 79146319
Accepted
Maury Markowitz
Asked: 2024-11-01 04:59:14 +0800 CST

以逗号列表或类似形式返回数组？

  • 772

我正在尝试使用 SQL Server 中的 OPENJSON 从 JSON 文件构建规范化表。JSON 的格式如下：

{
  "actions":[
     {"action":"delete","table":"users","key":4}],
     {"action":"update","table":"users","key":5,
      "fields":[{"Name":"FirstName":,"OldValue":"X","NewValue":"Bob"},
                {"Name":"LastName":,"OldValue":"Y","NewValue":"Dobbs"}]
}

我的目标是在动态 SQL 中使用这些条目来生成有效的查询：

UPDATE [users] SET [FirstName]='Bob',[LastName]='Dobbs' WHERE [key]=5

目前我正在使用这个 SQL：

SELECT * FROM OPENJSON(@json, '$.actions')
WITH (
  [Action] varchar(25) '$.action',
  [Table] varchar(25) '$.table',
  [Key] varchar(25) '$.key',
  [fields] nvarchar(MAX) AS JSON
 )

这给了我有用的结果，例如：

update   users    5    {"name":...

我最初的想法是使用字符串解析来分解字段中的数据，但现在我意识到这是个坏主意。我相信我可以从临时表中查询到 JSON 解析器并将其分解为行集，但我到处都找过了，找不到一个可以展示这一点的示例。

有人对如何处理长度不一的字段列表有什么建议吗？

sql

1 个回答

  1. Best Answer

    看起来您需要OUTER APPLY分解STRING_AGG并重新聚合该fields属性。

    另请注意

    • 用于sysname对象和列名称。
    • 用于QUOTENAME转义对象和列名，用于REPLACE长字符串。
    • 我没有考虑过INSERT，但你应该能够适应这一点。
    DECLARE @json nvarchar(max) = N'{
  "actions":[
     {"action":"delete","table":"users","key":4},
     {"action":"update","table":"users","key":5,
      "fields":[{"Name":"FirstName","OldValue":"X","NewValue":"Bob"},
                {"Name":"LastName","OldValue":"Y","NewValue":"Dobbs"}]
  }
]}';

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(
  UPPER(j1.action) +
    ' ' +
    QUOTENAME(j1.[table]) +
    CASE WHEN j1.action = 'update'
      THEN '
SET
' + updateFields.allCols
      ELSE ''
    END + '
WHERE [key] = ' +
    CAST(j1.[key] AS nvarchar(10)) +
    ';',
    '
  
')
FROM OPENJSON(@json, '$.actions')
  WITH (
    action nvarchar(25),
    [table] sysname,
    [key] int,
    fields nvarchar(MAX) AS JSON
  ) j1
OUTER APPLY (
    SELECT STRING_AGG(
      '  ' + QUOTENAME(j2.Name) + ' = ' + ISNULL('N''' + REPLACE(j2.NewValue, '''', '''''') + '''', 'NULL'),
      ',
'
    )
    FROM OPENJSON(j1.fields)
      WITH (
        Name sysname,
        NewValue nvarchar(max)
      ) j2
) updateFields(allCols)
WHERE j1.action IN ('update', 'delete');

PRINT @sql;  -- your friend

EXEC sp_executesql @sql;

    db<>小提琴

    • 2

相关问题