Build JSON out of Select but without column names in SQL Server 2016

As @Jeroen-Mostert notes, this is pretty simple to do in procedural code. You can even have SQL Server do it using SQL CLR. Because this shape is not natural for FOR JSON queries, a CLR-based solution is probably be better than a TSQL one.

Below concatenates the results into a single string, but you could change that to stream the results over multiple rows (like FOR JSON queries do), or add in GZip compression.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void RunQuery (string sql)
    {
        /*
         {"Response":{"Header":["A","B","C"],
                "Body":[["11","22","2018-04-07 12:44"],
                        ["33","44","2017-02-21 18:55"],
                        ["55","66","2016-11-12 00:03"]
                       ]
             }
         }
         * */
        using (var con = new SqlConnection("Context Connection=true"))
        {
            var sb = new StringBuilder();
            con.Open();
            var cmd = con.CreateCommand();
            cmd.CommandText = sql;
            using (var rdr = cmd.ExecuteReader())
            {
                sb.Append("{\"Response\":{\"Header\":[");
                for (int i = 0; i < rdr.FieldCount; i++)
                {
                    var fn = rdr.GetName(i);
                    sb.Append('"').Append(fn).Append('"');
                    if (i + 1 < rdr.FieldCount)
                        sb.Append(',');
                }
                sb.Append("],\"Body\":[");

                //SqlContext.Pipe.Send(sb.ToString());

                if (rdr.Read())
                {
                    while (true)
                    {

                        sb.Append('[');
                        for (int i = 0; i < rdr.FieldCount; i++)
                        {
                            var val = rdr[i].ToString();
                            sb.Append('"').Append(val).Append('"');
                            if (i + 1 < rdr.FieldCount)
                                sb.Append(',');
                        }
                        sb.Append(']');
                        if (rdr.Read())
                        {
                            sb.Append(',');
                        }
                        else
                        {
                            break;
                        }

                    }
                }
                sb.Append("]}}");

                var md = new SqlMetaData("JSON", SqlDbType.NVarChar,-1);


                var r = new SqlDataRecord(md);
                r.SetString(0, sb.ToString());
                SqlContext.Pipe.SendResultsStart(r);
                SqlContext.Pipe.SendResultsRow(r);
                SqlContext.Pipe.SendResultsEnd();

            }
        }
    }
}

This, however, makes the result several times larger than needed (I'm thinking about network traffic, specially when it is not over a LAN).

Given that:

1. your goal is to reduce network traffic, and
2. you are using a servlet to handle the DB communication
3. there are multiple datatypes involved

you can try...

1. simply not using JSON to return the result set to the servlet. Converting the result set to JSON is resource-intensive, requiring additional CPU, RAM, and time. And while JSON is a more efficient text format than XML, it is not necesarilly more efficient than sticking with the native TDS format. Consider that integers of 0 to 3 digits will be more efficient as text, 4 digits will be equally efficient, and 5 digits or more are less efficient. So for INT fields, which format is more efficient depends on the actual results. On the other hand, DATETIME fields are 8 bytes natively, but when serialized as text for JSON, it becomes 23 bytes. This, of course, assumes that you are converting the JSON to VARCHAR. If you are unable to convert to VARCHAR to return the JSON to the servlet due to having Unicode strings in the results, then those string dates take up 46 bytes each (SQL Server uses UTF-16 only for Unicode, there is no UTF-8 option). And that also changes the difference between INT values such that only values of 0 or 1 digits is more efficient as text, 2 digits is equivalent, and 3 or more digits are less efficient.

   The overall point being: you need to thoroughly test both approach because, I (and apparently others on here) suspect that you would, at best, end up with an equally efficient transport, yet would have paid the price in terms of having more convoluted code and require more system resources. Hence a net-negative. But, more than likely this approach will be slower, in addition to paying that price for it.

   So, as has already been suggested in various comments by @PanagiotisKanavos, @JeroenMostert, and @DavidBrowne: just convert to JSON in the servlet.

2. compressing the returned, standard JSON via the built-in COMPRESS function (introduced in SQL Server 2016). By simply adding a single function to the query you can reduce the returned JSON by over 90%. For example:

   DECLARE @Results VARCHAR(MAX);
   SET @Results = (SELECT * FROM sys.objects FOR JSON AUTO);
   SELECT @Results AS [JSON],
          DATALENGTH(@Results) AS [UncompressedBytes],
          DATALENGTH(COMPRESS(@Results)) AS [CompressedBytes];
   

   returns:

   [{"name":"sysrscols","object_id":3,"schema_id":4,"parent_object_id":0,"type":"S","type_desc":"SYSTEM_TABLE","create_date":"2017-08-22T19:38:02.860","modify_date":"2017-08-22T19:38:02.867","is_ms_shipped":true,"is_published":false,"is_schema_published":false},...

   29521

   2594

   This dramastically reduces the network traffic without convoluting the code or coming up with a proprietary format.

   The compression is done using GZip, which appears to be rather easy to handle in Java:

   Java GZIP Example – Compress and Decompress File

   Proving that you really can have your JSON and compress it too 😸

P.S. Regarding your comment, on David's answer, of:

I thought of attaching a JAVA stored procedure

No, this is not an option with SQL Server.

P.P.S. Regarding the comment that SQLCLR is deprecated, even just "effectively", please see my post:

SQLCLR vs SQL Server 2017, Part 8: Is SQLCLR Deprecated in Favor of Python or R (sp_execute_external_script)?