JSON Format conditionally shows number values as strings

[tylerlaws0n]

Please make sure that the version you're using is still supported (you can find the list here).

latest

You have to provide the following information whenever possible.

Describe what's wrong

A clear and concise description of what works not as it is supposed to.

For the JSON format (or any other JSON* format), number results are a number type if they come directly from a number column or if there is math done on them, such as division or multiplication. However, if they are the result of a clickhouse function such as sum, count, or length: the json type of the value is a string.

A link to reproducer in https://fiddle.clickhouse.com/.

https://fiddle.clickhouse.com/65fe294f-b2ce-4160-b0ab-628f829dcc8b

Does it reproduce on the most recent release?

Yes

How to reproduce

clickhouse fiddle link above

Expected behavior

I would expect every number type to exist in JSON as a number and not a string.

Error message and/or stacktrace

n/a

Additional context

n/a

[alexey-milovidov]

In ClickHouse, when using JSON* family formats, numbers larger than Int32 are represented as strings, as Int64+ types maximum values are larger than Number.MAX_SAFE_INTEGER. This is to avoid integer overflow as max values for these types are bigger than Number.MAX_SAFE_INTEGER. However, this behavior can be modified with output_format_json_quote_64bit_integers setting.

Here is an example of how to adjust the JSON output format for 64-bit numbers:

const resultSet = await client.query({  
 query: 'SELECT * from system.numbers LIMIT 1',  
 format: 'JSONEachRow',  
})  
  
expect(await resultSet.json()).toEqual([ { number: '0' } ])  

const resultSet = await client.query({  
 query: 'SELECT * from system.numbers LIMIT 1',  
 format: 'JSONEachRow',  
 clickhouse_settings: { output_format_json_quote_64bit_integers: 0 },  
})  
  
expect(await resultSet.json()).toEqual([ { number: 0 } ])  

In the first query, the number is returned as a string, while in the second query, with the output_format_json_quote_64bit_integers setting set to 0, the number is returned as a number.

However, it's important to note that there are some known limitations when using ClickHouse with Node.js/Web. For instance, when using JSON* family formats, numbers larger than Int32 are represented as strings, as Int64+ types maximum values are larger than Number.MAX_SAFE_INTEGER.

For more details, you can refer to the following documentation:

• Integral types: Int64, Int128, Int256, UInt64, UInt128, UInt256
• Known limitations (Node.js/Web)

[tylerlaws0n]

Thanks for the thorough explanation! Apologies for missing this in the docs as well.