Início / coding / Perguntas / 79146319
Accepted
Maury Markowitz
Asked: 2024-11-01 04:59:14 +0800 CST

Retornar uma matriz como uma lista de vírgulas ou similar?

  • 772

Estou tentando construir uma tabela normalizada a partir de um arquivo JSON usando OPENJSON no SQL Server. O JSON tem o formato:

{
  "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"}]
}

Meu objetivo é usar essas entradas no SQL dinâmico para produzir uma consulta funcional:

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

Atualmente estou usando este SQL:

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

O que me dá resultados úteis como:

update   users    5    {"name":...

Minha ideia original era usar análise de string para separar os dados em campos, mas agora percebo que é uma má ideia. Acredito que posso consultar isso de volta da tabela temporária para o analisador JSON e fazer com que ele os separe em um conjunto de linhas, mas procurei em todos os lugares e não consegui encontrar um exemplo que mostre isso.

Alguém tem algum conselho sobre como lidar com uma lista de campos de tamanhos variados?

sql

1 respostas

  1. Best Answer

    Parece que você precisa OUTER APPLYseparar STRING_AGGe reagregar a fieldspropriedade.

    Observe também

    • Use sysnamepara nomes de objetos e colunas.
    • Use QUOTENAMEpara escapar nomes de objetos e colunas, use REPLACEpara strings longas.
    • Não pensei nisso INSERT, mas você deve conseguir adaptar isso.
    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<>violino

    • 2

relate perguntas