In SQL Server 2016, you will be able to define the schema at query time when opening 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))
Please note that you cannot have a dynamic return scheme - you need to specify which fields should be returned to TVF. Alternatively, you can use openjson without a WITH clause to dynamically return all key pairs: from a JSON object:
select id, name, json.[key], json.value from tab cross apply openjson(value) as json
In this version, OPENJSON will return rotational values. key: value pairs will not be returned as a column: cell - each key pair: value will be returned in 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 ....
It will also be available in the Azure SQL database. In an earlier version, you will need to find or write a CLR TVF that parses JSON or uses extremely complex T-SQL. I can recommend JsonSelect and json4sql if you want to use existing CLR solutions. Another alternative for the old version of SQL Server is to use XML instead of JSON and use the nodes () function.