> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8c05c8a2.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Documentation for the JSONObjectEachRow format # JSONObjectEachRow | Input | Output | Alias | | ----- | ------ | ----- | | ✔ | ✔ | |

Description

In this format, all data is represented as a single JSON Object, with each row represented as a separate field of this object similar to the [`JSONEachRow`](/reference/formats/JSON/JSONEachRow) format.

Example usage

Basic example

Given some JSON: ```json theme={null} { "row_1": {"num": 42, "str": "hello", "arr": [0,1]}, "row_2": {"num": 43, "str": "hello", "arr": [0,1,2]}, "row_3": {"num": 44, "str": "hello", "arr": [0,1,2,3]} } ``` To use an object name as a column value you can use the special setting [`format_json_object_each_row_column_for_object_name`](/reference/settings/formats#format_json_object_each_row_column_for_object_name). The value of this setting is set to the name of a column, that is used as JSON key for a row in the resulting object.

Output

Let's say we have the table `test` with two columns: ```text theme={null} ┌─object_name─┬─number─┐ │ first_obj │ 1 │ │ second_obj │ 2 │ │ third_obj │ 3 │ └─────────────┴────────┘ ``` Let's output it in the `JSONObjectEachRow` format and use the `format_json_object_each_row_column_for_object_name` setting: ```sql title="Query" theme={null} SELECT * FROM test SETTINGS format_json_object_each_row_column_for_object_name='object_name' ``` ```json title="Response" theme={null} { "first_obj": {"number": 1}, "second_obj": {"number": 2}, "third_obj": {"number": 3} } ```

Input

Let's say we stored the output from the previous example in a file named `data.json`: ```sql title="Query" theme={null} SELECT * FROM file('data.json', JSONObjectEachRow, 'object_name String, number UInt64') SETTINGS format_json_object_each_row_column_for_object_name='object_name' ``` ```response title="Response" theme={null} ┌─object_name─┬─number─┐ │ first_obj │ 1 │ │ second_obj │ 2 │ │ third_obj │ 3 │ └─────────────┴────────┘ ``` It also works for schema inference: ```sql title="Query" theme={null} DESCRIBE file('data.json', JSONObjectEachRow) SETTING format_json_object_each_row_column_for_object_name='object_name' ``` ```response title="Response" theme={null} ┌─name────────┬─type────────────┐ │ object_name │ String │ │ number │ Nullable(Int64) │ └─────────────┴─────────────────┘ ```

Inserting data

```sql title="Query" theme={null} INSERT INTO UserActivity FORMAT JSONEachRow {"PageViews":5, "UserID":"4324182021466249494", "Duration":146,"Sign":-1} {"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1} ``` ClickHouse allows: * Any order of key-value pairs in the object. * Omitting some values. ClickHouse ignores spaces between elements and commas after the objects. You can pass all the objects in one line. You do not have to separate them with line breaks.

Omitted values processing

ClickHouse substitutes omitted values with the default values for the corresponding [data types](/reference/data-types/index). If `DEFAULT expr` is specified, ClickHouse uses different substitution rules depending on the [input\_format\_defaults\_for\_omitted\_fields](/reference/settings/formats#input_format_defaults_for_omitted_fields) setting. Consider the following table: ```sql title="Query" theme={null} CREATE TABLE IF NOT EXISTS example_table ( x UInt32, a DEFAULT x * 2 ) ENGINE = Memory; ``` * If `input_format_defaults_for_omitted_fields = 0`, then the default value for `x` and `a` equals `0` (as the default value for the `UInt32` data type). * If `input_format_defaults_for_omitted_fields = 1`, then the default value for `x` equals `0`, but the default value of `a` equals `x * 2`. When inserting data with `input_format_defaults_for_omitted_fields = 1`, ClickHouse consumes more computational resources, compared to insertion with `input_format_defaults_for_omitted_fields = 0`.

Selecting data

Consider the `UserActivity` table as an example: ```response theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 5 │ 146 │ -1 │ │ 4324182021466249494 │ 6 │ 185 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘ ``` The query `SELECT * FROM UserActivity FORMAT JSONEachRow` returns: ```response theme={null} {"UserID":"4324182021466249494","PageViews":5,"Duration":146,"Sign":-1} {"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1} ``` Unlike the [JSON](/reference/formats/JSON/JSON) format, there is no substitution of invalid UTF-8 sequences. Values are escaped in the same way as for `JSON`. Any set of bytes can be output in the strings. Use the [`JSONEachRow`](/reference/formats/JSON/JSONEachRow) format if you are sure that the data in the table can be formatted as JSON without losing any information.

Usage of Nested Structures

If you have a table with the [`Nested`](/reference/data-types/nested-data-structures/index) data type columns, you can insert JSON data with the same structure. Enable this feature with the [input\_format\_import\_nested\_json](/reference/settings/formats#input_format_import_nested_json) setting. For example, consider the following table: ```sql title="Query" theme={null} CREATE TABLE json_each_row_nested (n Nested (s String, i Int32) ) ENGINE = Memory ``` As you can see in the `Nested` data type description, ClickHouse treats each component of the nested structure as a separate column (`n.s` and `n.i` for our table). You can insert data in the following way: ```sql title="Query" theme={null} INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n.s": ["abc", "def"], "n.i": [1, 23]} ``` To insert data as a hierarchical JSON object, set [`input_format_import_nested_json=1`](/reference/settings/formats#input_format_import_nested_json). ```json theme={null} { "n": { "s": ["abc", "def"], "i": [1, 23] } } ``` Without this setting, ClickHouse throws an exception. ```sql title="Query" theme={null} SELECT name, value FROM system.settings WHERE name = 'input_format_import_nested_json' ``` ```response title="Response" theme={null} ┌─name────────────────────────────┬─value─┐ │ input_format_import_nested_json │ 0 │ └─────────────────────────────────┴───────┘ ``` ```sql title="Query" theme={null} INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}} ``` ```response title="Response" theme={null} Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: n: (at row 1) ``` ```sql title="Query" theme={null} SET input_format_import_nested_json=1 INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}} SELECT * FROM json_each_row_nested ``` ```response title="Response" theme={null} ┌─n.s───────────┬─n.i────┐ │ ['abc','def'] │ [1,23] │ └───────────────┴────────┘ ```

Format settings

| Setting | Description | Default | Notes | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | [`input_format_import_nested_json`](/reference/settings/formats#input_format_import_nested_json) | map nested JSON data to nested tables (it works for JSONEachRow format). | `false` | | | [`input_format_json_read_bools_as_numbers`](/reference/settings/formats#input_format_json_read_bools_as_numbers) | allow to parse bools as numbers in JSON input formats. | `true` | | | [`input_format_json_read_bools_as_strings`](/reference/settings/formats#input_format_json_read_bools_as_strings) | allow to parse bools as strings in JSON input formats. | `true` | | | [`input_format_json_read_numbers_as_strings`](/reference/settings/formats#input_format_json_read_numbers_as_strings) | allow to parse numbers as strings in JSON input formats. | `true` | | | [`input_format_json_read_arrays_as_strings`](/reference/settings/formats#input_format_json_read_arrays_as_strings) | allow to parse JSON arrays as strings in JSON input formats. | `true` | | | [`input_format_json_read_objects_as_strings`](/reference/settings/formats#input_format_json_read_objects_as_strings) | allow to parse JSON objects as strings in JSON input formats. | `true` | | | [`input_format_json_named_tuples_as_objects`](/reference/settings/formats#input_format_json_named_tuples_as_objects) | parse named tuple columns as JSON objects. | `true` | | | [`input_format_json_try_infer_numbers_from_strings`](/reference/settings/formats#input_format_json_try_infer_numbers_from_strings) | try to infer numbers from string fields while schema inference. | `false` | | | [`input_format_json_try_infer_named_tuples_from_objects`](/reference/settings/formats#input_format_json_try_infer_named_tuples_from_objects) | try to infer named tuple from JSON objects during schema inference. | `true` | | | [`input_format_json_infer_incomplete_types_as_strings`](/reference/settings/formats#input_format_json_infer_incomplete_types_as_strings) | use type String for keys that contains only Nulls or empty objects/arrays during schema inference in JSON input formats. | `true` | | | [`input_format_json_defaults_for_missing_elements_in_named_tuple`](/reference/settings/formats#input_format_json_defaults_for_missing_elements_in_named_tuple) | insert default values for missing elements in JSON object while parsing named tuple. | `true` | | | [`input_format_json_ignore_unknown_keys_in_named_tuple`](/reference/settings/formats#input_format_json_ignore_unknown_keys_in_named_tuple) | ignore unknown keys in json object for named tuples. | `false` | | | [`input_format_json_compact_allow_variable_number_of_columns`](/reference/settings/formats#input_format_json_compact_allow_variable_number_of_columns) | allow variable number of columns in JSONCompact/JSONCompactEachRow format, ignore extra columns and use default values on missing columns. | `false` | | | [`input_format_json_throw_on_bad_escape_sequence`](/reference/settings/formats#input_format_json_throw_on_bad_escape_sequence) | throw an exception if JSON string contains bad escape sequence. If disabled, bad escape sequences will remain as is in the data. | `true` | | | [`input_format_json_empty_as_default`](/reference/settings/formats#input_format_json_empty_as_default) | treat empty fields in JSON input as default values. | `false`. | For complex default expressions [`input_format_defaults_for_omitted_fields`](/reference/settings/formats#input_format_defaults_for_omitted_fields) must be enabled too. | | [`output_format_json_quote_64bit_integers`](/reference/settings/formats#output_format_json_quote_64bit_integers) | controls quoting of 64-bit integers in JSON output format. | `true` | | | [`output_format_json_quote_64bit_floats`](/reference/settings/formats#output_format_json_quote_64bit_floats) | controls quoting of 64-bit floats in JSON output format. | `false` | | | [`output_format_json_quote_denormals`](/reference/settings/formats#output_format_json_quote_denormals) | enables '+nan', '-nan', '+inf', '-inf' outputs in JSON output format. | `false` | | | [`output_format_json_quote_decimals`](/reference/settings/formats#output_format_json_quote_decimals) | controls quoting of decimals in JSON output format. | `false` | | | [`output_format_json_escape_forward_slashes`](/reference/settings/formats#output_format_json_escape_forward_slashes) | controls escaping forward slashes for string outputs in JSON output format. | `true` | | | [`output_format_json_named_tuples_as_objects`](/reference/settings/formats#output_format_json_named_tuples_as_objects) | serialize named tuple columns as JSON objects. | `true` | | | [`output_format_json_array_of_rows`](/reference/settings/formats#output_format_json_array_of_rows) | output a JSON array of all rows in JSONEachRow(Compact) format. | `false` | | | [`output_format_json_validate_utf8`](/reference/settings/formats#output_format_json_validate_utf8) | enables validation of UTF-8 sequences in JSON output formats (note that it doesn't impact formats JSON/JSONCompact/JSONColumnsWithMetadata, they always validate utf8). | `false` | |