Transform JSON data to separate columns

Question

I have following DB structure:

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"}
..........

I am looking for a query with the native functions of SQL Server 2012 to extract the JSON from the column Value, and dynamically create columns, and I want to do this for different count of columns without hard coding the column names name, icon, twitter, facebook. So the result I am looking for like:

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

If this is not possible with native SQL Server mechanisms, maybe PostgreSQL can do it, or other RMDBS

PS. My question is not duplicate of Parse JSON in TSQL. I need to find out the way to parse that heterogeneous json in rows

Answer 1

In SQL Server 2016 you would be able to define schema at query time when you open 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))

Note that you cannot have dynamic return schema - you need to specify what fields should be returned in TVF. As an alternative you can use openjson without WITH clause to dynamically return all key:value pairs from the JSON object:

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

In this version, OPENJSON will return pivoted values. key:value pairs will not be returned as column:cell - each key:value pair will be returned in a separate rows:

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

This will be also available in Azure SQL Database soon. In earlier version you will need to find or write some CLR TVF that parses JSON or use some extremely complicated T-SQL. I can recommend JsonSelect and json4sql if you want to use existing CLR solutions. Other alternative for older version of SQL Server is to use XML instead of JSON and use nodes() function.