SQL Server Express 2016 / 2017

[JTRTech]

He posted a nice little query the other day to get entity custom data which is stored in json via SQL;

Dual Pins for Users

Documenting to get FOB/User from CustomData (SQL 2016) … ##Query SELECT e.[Id] ,e.[Name] ,jsonData.jsonValue as [FOB] --,e.[CustomData] FROM [Entities] e JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId] JOIN [EntityCustomFields] cf on cf.[EntityTypeId] = e.[EntityTypeId] -- here we "join" the [Entities] table to itself and use the OPENJSON function -- on the [CustomData] column CROSS APPLY OPENJSON(e.[CustomData]) -- this WITH portion allows explicit definition of the schema JSON Keys for o…