Dual Pins for Users

V5 Request
37

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 output
-- and gives references to the columns/field above in the SELECT portion
WITH (
  jsonName  varchar(50) '$.Name'
 ,jsonValue varchar(50) '$.Value'
) jsonData
WHERE et.[Name] = 'Employees'
AND cf.[Name] = 'FOB'
AND jsonName = 'FOB'

##Function

function getEntityByCustomDataFieldValue(entityType,fieldName,fieldValue) {
  var qry = "SELECT e.[Name] FROM [Entities] e JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId] JOIN [EntityCustomFields] cf on cf.[EntityTypeId] = e.[EntityTypeId] CROSS APPLY OPENJSON(e.[CustomData]) WITH (jsonName varchar(50) '$.Name', jsonValue varchar(50) '$.Value') jsonData WHERE et.[Name] = '"+entityType+"' AND cf.[Name] = '"+fieldName+"' AND jsonName = 'FOB' AND jsonValue='"+fieldValue+"'";
  var userName = sql.Query(qry).First;
  return userName || 'NOTFOUND';
}

Sample Calls:

getEntityByCustomDataFieldValue('Employees','FOB','123456') // returns Entity Name 'Q'
getEntityByCustomDataFieldValue('Employees','FOB','135')    // returns 'NOTFOUND'
2 Likes