declare @ownerId int = ; declare @ownerType varchar(3) = ; with rawData as ( select '#_domain#' as domain, f.fieldId, upper( rtrim( ltrim( f.name ))) name, upper( rtrim( ltrim( f.category ))) category, rtrim( ltrim( f.label )) label, f.type, f.required, f.format, f.config, f.sortRank, f.maxLength, f.lockEdit, f.active, f.searchable, f.lookupValueSortMode, json_query( case when f.config = 'cfquery=lookup' then ( select fl.selectionValue as [value], fl.selectionLabel as [text], fl.sortRank, sourceFieldId [sourceFieldId], sourceFieldOp [sourceFieldOp], sourceFieldValue [sourceFieldValue] cast( null as int ) sourceFieldId, cast( null as varchar) sourceFieldOp, cast( null as varchar ) sourceFieldValue from tbl_#_domain#_FieldLookups fl with (nolock) left outer join tbl_WRK_Conditions c with (nolock) on c.ownerId = fl.ownerId and c.ownerType = fl.ownerType and c.targetType = 'L' and c.targetKey = cast( f.fieldId as varchar ) + ':' + fl.selectionValue where fl.ownerId = f.ownerId and fl.ownerType = f.ownerType and fl.fieldName = f.name order by fl.sortRank, [text] for json path, include_null_values ) else ( null ) end ) as lookupValues, c.sourceFieldId, c.sourceFieldOp, c.sourceFieldValue cast( null as int ) sourceFieldValue, cast( null as varchar ) sourceFieldOp, cast( null as varchar ) sourceFieldValue from tbl_#_domain#_Fields f with (nolock) left outer join tbl_WRK_Conditions c with (nolock) on c.ownerId = f.ownerId and c.ownerType = f.ownerType and c.targetType = 'F' and c.targetKey = cast( f.fieldId as varchar ) where f.ownerId = @ownerId and f.ownerType = @ownerType ) , data as ( select *, category + '_' + name as fieldName from rawData where 1 = 1 and category = and type in ( ) ) #forJSON( 'data', attributes.datastore, 'category,sortRank,label' )#