将JSON数据转换为单独的列

Question

我有以下数据库结构：

ID  Name  Value
1   TV1   {"URL": "www.url.com", "Icon": "some_icon"}
2   TV2   {"URL": "www.url.com", "Icon": "some_icon", "Facebook": "Facebook_URL"}
3   TV3   {"URL": "www.url.com", "Icon": "some_icon", "Twitter": "Twitter_URL"}
..........

我正在寻找使用SQL Server 2012的原生函数从列Value中提取JSON，并动态创建列的查询，我希望能够针对不同数量的列进行操作，而不需要硬编码列名name、icon、twitter和facebook。因此，我期望的结果如下：

ID  Name  URL          Icon           Facebook      Twitter
1   TV1   www.url.com  some_icon          NULL         NULL
2   TV2   www.url.com  some_icon  Facebook_URL         NULL
3   TV3   www.url.com  some_icon          NULL  Twitter_URL

如果使用本地SQL Server机制无法实现，也许PostgreSQL或其他RMDBS可以做到。

PS. 我的问题不是在TSQL中解析JSON的重复。我需要找出在行中解析异构JSON的方法。

- SpanishBoy

---

你想做什么？你想将这个JSON结构导入到SQL Server表中吗？ - Mahmoud Gamal

我需要编写一个查询来从列中提取JSON，并动态创建列。所有细节请参见说明。谢谢！ - SpanishBoy

据我所知，使用SQL Server 2016应该是可以实现的。SQL Server 2016中的JSON支持 - Evaldas Buinauskas

可能是在TSQL中解析JSON的重复问题。 - Evaldas Buinauskas

关于转换有一个具体的问题，这不像上面的链接中那样是一个普遍性的问题。如果我错了，请纠正我。 - SpanishBoy

Answer 1

在SQL Server 2016中，您可以在打开JSON时定义查询时间的架构：

select id, name, url, icon, facebook, twitter
from tab
     cross apply openjson(value) 
                 with (url nvarchar(100), icon varbinary,
                       facebook nvarchar(100),twitter nvarchar(100))

请注意，您不能拥有动态返回模式 - 您需要在TVF中指定应返回哪些字段。作为替代方案，您可以使用不带WITH子句的openjson来动态返回JSON对象中的所有键值对：

select id, name, json.[key], json.value
from tab
     cross apply openjson(value) as json

在这个版本中，OPENJSON将返回旋转的值。键值对不会作为列:单元格返回 - 每个键值对将在单独的行中返回：

ID Name key      value
1  TV1  URL      www.url.com
1  TV1  Icon     some_icon
2  TV2  URL      www.url.com
2  TV2  Icon     some_icon
2  TV3  Facebook Facebook_URL
3  TV3  URL      www.url.com
3  TV3  Icon     some_icon
3  TV3  Twitter  Twitter_URL
....

这将很快在Azure SQL数据库中也可用。 在早期版本中，您需要找到或编写一些CLR TVF来解析JSON，或者使用一些非常复杂的T-SQL。如果您想使用现有的CLR解决方案，则可以推荐JsonSelect和json4sql。

SQL Server旧版的另一种选择是使用XML而不是JSON，并使用nodes()函数。

- Jovan MSFT

Answer 2

请看一下，如果这篇文章能帮到您，请告诉我们。

https://www.simple-talk.com/blogs/2013/03/26/sql-server-json-to-table-and-table-to-json/

- Erlis Vidal

---

谢谢！目前对我来说最困难的问题是针对每一行迭代异构JSON内容。 - SpanishBoy