Reproducing ClickHouse Client CLI query result format using Golang ClickHouse library

Question

Clickhouse CLI result

When performing a query using the clickhouse-client command line interface and setting the format as JSON, we not only obtain the result but also statistics.

Command

clickhouse-client --password=test --user=default --format=JSON --query="select 1 + 1"

Result

{
    "meta":
    [
        {
            "name": "plus(1, 1)",
            "type": "UInt16"
        }
    ],

    "data":
    [
        {
            "plus(1, 1)": 2
        }
    ],

    "rows": 1,

    "statistics":
    {
        "elapsed": 0.001043427,
        "rows_read": 1,
        "bytes_read": 1
    }
}

I tried using the official ClickHouse library in Golang (github.com/ClickHouse/clickhouse-go/v2) but was unsuccessful in achieving the desired result.

If anyone has successfully obtained a JSON response using the Golang library, I would appreciate any insights or solutions shared.

Answer 1

When you a run a query using the Go client, specifying a format like JSON probably isn't going to work like you would expect. If you want to output the result in JSON, then you need to process each row and convert it to JSON.

It's actually not too difficult if you use the encoding/json library. Put the results in a Struct and use the json.MarshalIndent function:

    var r struct {
         Name string `ch:"Name"`
         Uuid_str string `ch:"Uuid_str"`
    }
    ctx := context.Background()
    err = conn.QueryRow(ctx, "SELECT name as Name,toString(uuid) as Uuid_str FROM system.tables LIMIT 1").ScanStruct(&r)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(r)
    file, _ := json.MarshalIndent(r, "", " ")
    _ = ioutil.WriteFile("test.json", file, 0644)

The result in test.json was:

{
 "Name": "COLUMNS",
 "Uuid_str": "00000000-0000-0000-0000-000000000000"
}