如何在SQL中为每行生成列值组合的JSON并插入至JSONDoc列

不同SQL数据库的实现方式略有差异，以下是主流数据库的解决方案：

SQL Server #

使用FOR JSON AUTO生成单行JSON对象，配合WITHOUT_ARRAY_WRAPPER去掉外层数组括号，更新到JSONDoc列：

-- 更新现有表的JSONDoc列
UPDATE YourTableName
SET JSONDoc = (
    SELECT * 
    FROM YourTableName t 
    WHERE t.PrimaryKeyColumn = YourTableName.PrimaryKeyColumn 
    FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
)

注意：将PrimaryKeyColumn替换为表的主键或唯一标识列，确保能准确匹配每行数据。

如果是插入数据时同时生成JSON：

INSERT INTO TargetTable (Column1, Column2, JSONDoc)
SELECT 
    Column1, 
    Column2,
    (SELECT * FROM SourceTable s WHERE s.PrimaryKey = SourceTable.PrimaryKey FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER)
FROM SourceTable

MySQL #

手动指定列（适合列数较少的情况） #

用JSON_OBJECT函数直接构建JSON：

UPDATE YourTableName
SET JSONDoc = JSON_OBJECT(
    'PartitionKey', PartitionKey,
    'SourceType', SourceType,
    'SourceKey', SourceKey,
    'OrganizationOwningAttID', OrganizationOwningAttID,
    'OrganizationGUID', OrganizationGUID,
    'OrganizationID', OrganizationID,
    'OrganizationName', OrganizationName
);

动态生成（适合列数较多的情况） #

通过动态SQL自动获取所有列并生成JSON：

SET @sql = NULL;
SELECT GROUP_CONCAT(CONCAT('''', COLUMN_NAME, ''', ', COLUMN_NAME)) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YourDatabaseName' AND TABLE_NAME = 'YourTableName';

SET @sql = CONCAT('UPDATE YourTableName SET JSONDoc = JSON_OBJECT(', @sql, ');');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL #

使用row_to_json函数直接将整行转为JSON对象：

-- 更新所有行的JSONDoc列（排除JSONDoc自身避免循环）
UPDATE YourTableName
SET JSONDoc = row_to_json((
    SELECT x FROM (
        SELECT PartitionKey, SourceType, SourceKey, OrganizationOwningAttID, OrganizationGUID, OrganizationID, OrganizationName
    ) x
))::text;

如果不需要排除列，可简化为：

UPDATE YourTableName
SET JSONDoc = row_to_json(YourTableName)::text;

以上方案生成的JSON结构将与你提供的示例一致，包含每行所有列的键值对。

内容的提问来源于stack exchange，提问作者tumblewood