private static string GenerateColumnsAsJson()

in src/Core/Resolvers/DWSqlQueryBuilder.cs [291:380]
65 lines of code
13 McCabe index (conditional complexity)

        private static string GenerateColumnsAsJson(SqlQueryStructure structure, bool subQueryStructure = false)
        {
            string columns;
            StringBuilder stringAgg = new();
            int columnCount = 0;
            // Iterate through all the columns and build the string_agg
            foreach (LabelledColumn column in structure.Columns)
            {
                // Generate the col value.
                bool subQueryColumn = structure.IsSubqueryColumn(column);
                string col_value = column.Label;
                string escapedLabel = column.Label.Replace("'", "''");

                // If the column is not a subquery column and is not a string, cast it to string
                if (!subQueryColumn && structure.GetColumnSystemType(column.ColumnName) != typeof(string))
                {
                    col_value = $"CONVERT(NVARCHAR(MAX), [{col_value}])";

                    Type col_type = structure.GetColumnSystemType(column.ColumnName);

                    if (col_type == typeof(DateTime))
                    {
                        // Need to wrap datetime in quotes to ensure correct deserialization.
                        stringAgg.Append($"N\'\"{escapedLabel}\":\"\' + ISNULL(STRING_ESCAPE({col_value},'json'),'null') + \'\"\'+");
                    }
                    else if (col_type == typeof(Boolean))
                    {
                        stringAgg.Append($"N\'\"{escapedLabel}\":\' + ISNULL(IIF({col_value} = 1, 'true', 'false'),'null')");
                    }
                    else
                    {
                        // Create json. Example: "book.id": 1 would be a sample output.
                        stringAgg.Append($"{BuildJson(escapedLabel, col_value)},'null')");
                    }
                }
                else
                {
                    // Create json. Example: "book.title": "Title" would be a sample output.
                    stringAgg.Append($"N\'\"{escapedLabel}\":\' + ISNULL(\'\"\'+STRING_ESCAPE([{col_value}],'json')+\'\"\','null')");
                }

                columnCount++;

                // Add comma if not last column. example: {"id":"1234","name":"Big Company"}
                // the below ensures there is a comma after id but not after name.
                if (columnCount != structure.Columns.Count)
                {
                    stringAgg.Append("+\',\'+");
                }
            }

            int aggregationColumnCount = 0;
            // Handle aggregation columns
            foreach (AggregationOperation aggregation in structure.GroupByMetadata.Aggregations)
            {
                if (aggregationColumnCount == 0 && columnCount != 0)
                {
                    // need to add a comma if there are columns before the aggregation columns
                    stringAgg.Append("+\', \'+");
                }

                string col_value = aggregation.Column.OperationAlias;
                col_value = $"CONVERT(NVARCHAR(MAX), [{col_value}])";
                string escapedLabel = aggregation.Column.OperationAlias.Replace("'", "''");

                stringAgg.Append($"{BuildJson(escapedLabel, col_value)},'null')");

                aggregationColumnCount++;

                if (aggregationColumnCount != structure.GroupByMetadata.Aggregations.Count)
                {
                    stringAgg.Append("+\',\'+");
                }
            }

            columns = $"STRING_AGG(\'{{\'+{stringAgg}+\'}}\',', ')";
            if (structure.IsListQuery)
            {
                // Array wrappers if we are trying to get a list of objects.
                columns = $"COALESCE(\'[\'+{columns}+\']\',\'[]\')";
            }
            else if (!subQueryStructure)
            {
                // outer apply sub queries can return null as that will be stored in the json.
                // However, for the main query, we need to return an empty string if the result is null as the sql cant read the NULL
                columns = $"COALESCE({columns},\'\')";
            }

            return columns;
        }