JSONAsString

Input	Output	Alias
✔	✗

Description

In this format, a single JSON object is interpreted as a single value. If the input has several JSON objects (which are comma separated), they are interpreted as separate rows. If the input data is enclosed in [], it is interpreted as an array of JSON objects.

Note

This format can only be parsed for a table with a single field of type String. The remaining columns must be set to either DEFAULT or MATERIALIZED, or be omitted.

Once you serialize the entire JSON object to a String you can use the JSON functions to process it.

Example usage

Basic example

DROP TABLE IF EXISTS json_as_string;
CREATE TABLE json_as_string (json String) ENGINE = Memory;
INSERT INTO json_as_string (json) FORMAT JSONAsString {"foo":{"bar":{"x":"y"},"baz":1}},{},{"any json stucture":1}
SELECT * FROM json_as_string;

┌─json──────────────────────────────┐
│ {"foo":{"bar":{"x":"y"},"baz":1}} │
│ {}                                │
│ {"any json stucture":1}           │
└───────────────────────────────────┘

An array of JSON objects

CREATE TABLE json_square_brackets (field String) ENGINE = Memory;
INSERT INTO json_square_brackets FORMAT JSONAsString [{"id": 1, "name": "name1"}, {"id": 2, "name": "name2"}];

SELECT * FROM json_square_brackets;

┌─field──────────────────────┐
│ {"id": 1, "name": "name1"} │
│ {"id": 2, "name": "name2"} │
└────────────────────────────┘

Format settings