逗号分隔的字符串到 JSON 对象

劳伦斯·科伦坡

我需要更新/迁移IdsTableSQL Server 数据库中具有以下格式的表:

+----+------------------+---------+
| id |       ids        | idType  |
+----+------------------+---------+
|  1 | id11, id12, id13 | idType1 |
|  2 | id20             | idType2 |
+----+------------------+---------+

ids列是一个逗号分隔的 id 列表。我需要组合idsidType列以形成每行的单个 JSON 字符串,并ids使用该对象更新列。

JSON 对象具有以下格式:

{
   "idType": string,
   "ids": string[]
}

转换/迁移数据后的最终表应为:

+----+-----------------------------------------------------+---------+
| id |                         ids                         | idType  |
+----+-----------------------------------------------------+---------+
|  1 | {"idType": "idType1","ids": ["id11","id12","id13"]} | idType1 |
|  2 | {"idType": "idType2","ids": ["id20"]}               | idType2 |
+----+-----------------------------------------------------+---------+

到目前为止,我想出的最好方法是将结果转换为一种格式,以便我可以GROUP BY id尝试获取正确的 JSON 格式:

SELECT X.id, Y.value, X.idType
FROM 
    IdsTable AS X 
    CROSS APPLY STRING_SPLIT(X.ids, ',') AS Y

这给了我结果:

+----+------+---------+
| id | ids  | idType  |
+----+------+---------+
|  1 | id11 | idType1 |
|  1 | id12 | idType1 |
|  1 | id13 | idType1 |
|  2 | id20 | idType2 |
+----+------+---------+

但我对 SQL Server JSON 不够熟悉,无法继续前进。

劳伦斯·科伦坡

我发现这篇博文帮助我构建了我的答案:

-- Create Temporary Table
SELECT 
    [TAB].[id], [TAB].[ids],
    (
        SELECT [STRING_SPLIT_RESULTS].value as [ids], [TAB].[idType] as [idType]
        FROM [IdsTable] AS [REQ] 
        CROSS APPLY STRING_SPLIT([REQ].[ids],',') AS [STRING_SPLIT_RESULTS]
        FOR JSON PATH
    ) as [newIds]
INTO [#TEMP_RESULTS]
FROM [IdsTable] AS [TAB] 

-- Update rows
UPDATE [IdsTable]
SET [ids] = [#TEMP_RESULTS].[newIds]  
FROM [#TEMP_RESULTS]
WHERE [IdsTable].[Id] = [#TEMP_RESULTS].[Id]

-- Delete Temporary Table
DROP TABLE [#TEMP_RESULTS]

哪个替换了那些ids列(下面没有替换以进行比较):


+----+----------------+---------+------------------------------------------------------------------------------------------------------+
| id |      ids       | idType  |                                                newIds                                                |
+----+----------------+---------+------------------------------------------------------------------------------------------------------+
|  1 | id11,id12,id13 | idType1 | [{"id":"id11","idType":"idType1"},{"id":"id12","idType":"idType1"},{"id":"id13","idType":"idType1"}] |
|  2 | id20           | idType2 | [{"id":"id20","idType":"idType2"}]                                                                   |
+----+----------------+---------+------------------------------------------------------------------------------------------------------+

这比我想要的更冗长,但考虑到表大小和ids列中存储的 id 数量(转换为 JSON 对象的大小),这对我来说很好。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章