with data as ( select 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, json_query( case when f.config = 'cfquery=lookup' then ( select fl.selectionValue as [value], fl.selectionLabel as [text], fl.sortRank from tbl_#_domain#_FieldLookups fl with (nolock) where fl.ownerId = f.ownerId and fl.ownerType = f.ownerType and fl.fieldName = f.name order by fl.sortRank, [text] for json auto ) else ( null ) end ) as lookupValues, '#_domain#' as flexDomain from tbl_#_domain#_Fields f with (nolock) where f.ownerId = and f.ownerType = and f.active = 'Y' and f.type != 'FILE' ) #forJSON( 'data', attributes.datastore, 'category,sortRank,label' )#