I want to generate JSON from two usual clickhouse columns and value of one such column has backslash in its containment.
First I create table
<code>CREATE TABLE app_events.al_test (col1 String,col2 String) ENGINE = MergeTree ORDER BY col1;
</code>
<code>CREATE TABLE app_events.al_test (col1 String,col2 String) ENGINE = MergeTree ORDER BY col1;
</code>
CREATE TABLE app_events.al_test (col1 String,col2 String) ENGINE = MergeTree ORDER BY col1;
Then I insert value with backslash , I double backslash to make the insert valid
<code>INSERT INTO app_events.al_test VALUES ('value\' , 'value1')
</code>
<code>INSERT INTO app_events.al_test VALUES ('value\' , 'value1')
</code>
INSERT INTO app_events.al_test VALUES ('value\' , 'value1')
Next I check and see that i have value ‘value’ with one backslash in it as expected.
<code>SELECT * FROM app_events.al_test
</code>
<code>SELECT * FROM app_events.al_test
</code>
SELECT * FROM app_events.al_test
Next I want create JSON from this values but because of single backslash is special character I need to double it but I can’t because statement become invalid. I mean to do replacement of '' to '\' I use replace function replace(J_son ,'','\') but this is not correct SQL statement this way.
<code>SELECT
'{'||'"'||col1||'"'||':'||'"'||col2||'"'||'}' AS J_son ,
isValidJSON(J_son),
--replace(J_son ,'','\')
FROM app_events.al_test
</code>
<code>SELECT
'{'||'"'||col1||'"'||':'||'"'||col2||'"'||'}' AS J_son ,
isValidJSON(J_son),
--replace(J_son ,'','\')
FROM app_events.al_test
</code>
SELECT
'{'||'"'||col1||'"'||':'||'"'||col2||'"'||'}' AS J_son ,
isValidJSON(J_son),
--replace(J_son ,'','\')
FROM app_events.al_test
how to handle such problem ?
UPD.
Thank you /users/320615/dogbert for your reply
Can you please help with following :
Same issue but another column types
<code>CREATE TABLE app_events.al_test ( col1 Array(String),col2 Array(String)) ENGINE = MergeTree ORDER BY tuple();
</code>
<code>CREATE TABLE app_events.al_test ( col1 Array(String),col2 Array(String)) ENGINE = MergeTree ORDER BY tuple();
</code>
CREATE TABLE app_events.al_test ( col1 Array(String),col2 Array(String)) ENGINE = MergeTree ORDER BY tuple();
Insert data , notice we have slash in color key :
<code>INSERT INTO app_events.al_test VALUES (['Car','Color','Year'] , ['BMW','Red\','1990']);
</code>
<code>INSERT INTO app_events.al_test VALUES (['Car','Color','Year'] , ['BMW','Red\','1990']);
</code>
INSERT INTO app_events.al_test VALUES (['Car','Color','Year'] , ['BMW','Red\','1990']);
I need achieve single json object which I can you as dict for separate column population ( for example I will have separate Car and Color columns which will be populated based on JSONExtractString(JSON,’Car’) and JSONExtractString(JSON,’Color’) )
How I had achieved this :
<code>SELECT
SUBSTRING(
'{'||arrayStringConcat(arrayMap((k,v) -> ('"'||k||'"'||':'||'"'||v||'"'),col1,col2))||'}'||',' AS J_son,
1,
LENGTH(J_son)-1) AS JS_ON,
isValidJSON(JS_ON)
from
app_events.al_test
</code>
<code>SELECT
SUBSTRING(
'{'||arrayStringConcat(arrayMap((k,v) -> ('"'||k||'"'||':'||'"'||v||'"'),col1,col2))||'}'||',' AS J_son,
1,
LENGTH(J_son)-1) AS JS_ON,
isValidJSON(JS_ON)
from
app_events.al_test
</code>
SELECT
SUBSTRING(
'{'||arrayStringConcat(arrayMap((k,v) -> ('"'||k||'"'||':'||'"'||v||'"'),col1,col2))||'}'||',' AS J_son,
1,
LENGTH(J_son)-1) AS JS_ON,
isValidJSON(JS_ON)
from
app_events.al_test
Script above generates correct JSON object if we had not backslash in one of values
How to handle this by using mechanism that you mentioned ?
There is no need to do manual escaping. Create a map of col1 -> col2 using map(col1, col2) and then pass that to toJSONString:
<code>:) select toJSONString(map(col1, col2)) as json, isValidJSON(json) from al_test;
SELECT
toJSONString(map(col1, col2)) AS json,
isValidJSON(json)
FROM al_test
┌─json─────────────────┬─isValidJSON(json)─┐
1. │ {"value\":"value1"} │ 1 │
└──────────────────────┴───────────────────┘
</code>
<code>:) select toJSONString(map(col1, col2)) as json, isValidJSON(json) from al_test;
SELECT
toJSONString(map(col1, col2)) AS json,
isValidJSON(json)
FROM al_test
┌─json─────────────────┬─isValidJSON(json)─┐
1. │ {"value\":"value1"} │ 1 │
└──────────────────────┴───────────────────┘
</code>
:) select toJSONString(map(col1, col2)) as json, isValidJSON(json) from al_test;
SELECT
toJSONString(map(col1, col2)) AS json,
isValidJSON(json)
FROM al_test
┌─json─────────────────┬─isValidJSON(json)─┐
1. │ {"value\":"value1"} │ 1 │
└──────────────────────┴───────────────────┘
Can be achieved following way
<code> SELECT toJSONString(mapFromArrays(col1, col2)) as json FROM app_events.al_test
</code>
<code> SELECT toJSONString(mapFromArrays(col1, col2)) as json FROM app_events.al_test
</code>
SELECT toJSONString(mapFromArrays(col1, col2)) as json FROM app_events.al_test