Is it possible to output raw JSON when using TabSeparated format?

[iosifnicolae2]

Context

• query using JSON format:

SELECT unit_id, raw_record FROM "default".unit_records LIMIT 1 FORMAT JSON

{
    "meta": [
        {
            "name": "unit_id",
            "type": "String"
        },
        {
            "name": "raw_record",
            "type": "Tuple(avl_1 Int8, avl_10349 Int8,  timestamp String)"
        }
    ],
    "data": [
        {
            "unit_id": "100001",
            "raw_record": {
                "avl_1": 1,
                "avl_10349": 0,
                "timestamp": "2022-06-21T13:29:05.060Z"
            }
        }
    ],
    "rows": 1,
    "rows_before_limit_at_least": 1,
    "statistics": {
        "elapsed": 0.008766542,
        "rows_read": 2,
        "bytes_read": 350
    }
}

• query using TabSeparated format:

SELECT unit_id, raw_record FROM "default".unit_records LIMIT 1 FORMAT TabSeparated

100001	(1,0,'2022-06-21T13:29:05.060Z')

Question

Is it possible to output raw JSON when using TabSeparated format?
eg.

100001	{""avl_1"":1,""avl_10349"":0,""timestamp"":""2022-06-21T13:29:05.060Z""}

Thank you!

PS. If you can point me how should I start implementing this, please let me know.

[UnamedRus]

SELECT
    num,
    formatRowNoNewline('JSONEachRow', res) AS a,
    JSONExtractRaw(formatRowNoNewline('JSONEachRow', res), 'res')
FROM
(
    SELECT
        111 AS num,
        CAST((1, 0, '2022-06-21T13:29:05.060Z'), 'Tuple(avl_1 Int8, avl_10349 Int8, timestamp String)') AS res
)
FORMAT TSVRaw

Query id: 77c48770-86fb-4258-91e4-2590cb9619a2

111     {"res":{"avl_1":1,"avl_10349":0,"timestamp":"2022-06-21T13:29:05.060Z"}}        {"avl_1":1,"avl_10349":0,"timestamp":"2022-06-21T13:29:05.060Z"}

[iosifnicolae2]

Yes, your solution might work but in our case Clickhouse is integrated into an ecosistem where the queries are generated automatically (in our case by Hasura).

Here is our use-case:

1. We've connected Hasura to PostgreSQL
2. Then we've installed clickhouse_fdw in PostgreSQL
3. We're trying to add support for json for clickhouse_fdw -Add "json" support ildus/clickhouse_fdw#88 and the missing piece is a way to output the raw json value like I've described above.

Temporary solution

Change the type of raw_record column to STRING.