How to represent a multi-line string of JSON?

[Alex-Cheng]

I want to write a SQL like following:

set param_json1={
    "a":1,
    "b":2,
    ... ...
}

select foo({json1:String});

But the SQL does not work. Is there any approach to do this?

Thank you in advance.

[alexey-milovidov]

In your example, the json1 parameter has a String type, which means that it has to be provided as a String, e.g.,

set param_json1='{
    "a":1,
    "b":2
}'

select foo({json1:String});

Surprisingly to me, it produces an error:

Code: 457. DB::Exception: Value {
    "a":1,
    "b":2
} cannot be parsed as String for query parameter 'json1' because it isn't parsed completely: only 1 of 24 bytes was parsed: {. (BAD_QUERY_PARAMETER)

[evillique]

param_ settings are translated into String type after parsing automatically, so you can use it after as a String in substitution.

However, these parameters must be a literal, Array/Map/Tuple of literals, or a combination of them written in ClickHouse syntax. In ClickHouse double quotes (") are used for identifiers and you can't have those inside a literal.

Setting parameters like this is supported:

SET param_json1={
    'a':1,
    'b':2
};

SELECT {json1:String} AS str, {json1:Map(String, UInt64)} AS map;

┌─str────────────┬─map───────────┐
│ {'a':1, 'b':2} │ {'a':1,'b':2} │
└────────────────┴───────────────┘

If the parameter you are trying to set has some other format then you will have to use String literal. Inside this literal the parameter must be in "escaped" format (https://clickhouse.com/docs/en/interfaces/http#tabs-in-url-parameters).

So in your example it would be:

SET param_json1='{\\n    "a":1,\\n    "b":2,\\n};'

SELECT {json1:String} AS str;

┌─str─────────────────────┐
│ {
    "a":1,
    "b":2,
}; │
└─────────────────────────┘

[alexey-milovidov]

@evillique It is ok to require Escaped format in HTTP interface and in command line arguments, because it is the only way to distinguish between a string and NULL.

But when the parameter is provided as an explicit string literal, it is questionable.

[Alex-Cheng]

@evillique It is ok to require Escaped format in HTTP interface and in command line arguments, because it is the only way to distinguish between a string and NULL.

But when the parameter is provided as an explicit string literal, it is questionable.

Thank you for responding my question. Actually I have a very long JSON where strings are double-quoted(because it is standard JSON format). So if I cannot use multi-line string as parameter nor double-quote in JSON, then it would be inconvenient.

[Alex-Cheng]

@evillique It is ok to require Escaped format in HTTP interface and in command line arguments, because it is the only way to distinguish between a string and NULL.
But when the parameter is provided as an explicit string literal, it is questionable.

Thank you for responding my question. Actually I have a very long JSON where strings are double-quoted(because it is standard JSON format). So if I cannot use multi-line string as parameter nor double-quote in JSON, then it would be inconvenient.

I have tried more. The RapidJSONParser cannot parse a JSON with single-quoted key, such as {'A':1, 'B':2}. If I use standard JSON format, such as {"A":1, "B":2}, parsing succeeds.