Return an array as a comma-list or similar?

I'm trying to construct a normalized table from a JSON file using OPENJSON in SQL Server. The JSON is of the form:

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

My goal is to use these entries in Dynamic SQL to produce a working query:

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

Currently I'm using this SQL:

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

Which gives me useful results like:

update   users    5    {"name":...

My original idea was to use string parsing to break out the data in fields, but now I realize that is bad idea. I believe I can query that back out of the temp table and into the JSON parser and have it break it out into a rowset, but I have looked everywhere and can't find an example that shows this.

Does anyone have any advice on how to deal with the varying-length list of fields?

Solution

It looks like you need OUTER APPLY and STRING_AGG to break out and re-aggregate the fields property.

Note also

Use sysname for object and column names.
Use QUOTENAME to escape object and column names, use REPLACE for long strings.
I haven't considered INSERT but you should be able to adapt this.

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<>fiddle