Handling other JSON formats

```
SELECT *
FROM file('list-nested.json', JSONEachRow, 'page Tuple(path String, title String, owner_id UInt16), month Date, hits UInt32')
LIMIT 1
```

```
┌─page───────────────────────────────────────────────┬──────month─┬─hits─┐
│ ('Akiba_Hebrew_Academy','Akiba Hebrew Academy',12) │ 2017-08-01 │  241 │
└────────────────────────────────────────────────────┴────────────┴──────┘
```

## Accessing nested JSON objects[​](#accessing-nested-json-objects "Direct link to Accessing nested JSON objects")

We can refer to [nested JSON keys](/docs/assets/files/list-nested-ea2507b8a6fd7aabe23665516cf650e2.json) by enabling the [following settings option](/docs/operations/settings/formats#input_format_import_nested_json):

```
SET input_format_import_nested_json = 1
```

This allows us to refer to nested JSON object keys using dot notation (remember to wrap those with backtick symbols to work):

```
SELECT *
FROM file('list-nested.json', JSONEachRow, '`page.owner_id` UInt32, `page.title` String, month Date, hits UInt32')
LIMIT 1
```

```
┌─page.owner_id─┬─page.title───────────┬──────month─┬─hits─┐
│            12 │ Akiba Hebrew Academy │ 2017-08-01 │  241 │
└───────────────┴──────────────────────┴────────────┴──────┘
```

This way we can flatten nested JSON objects or use some nested values to save them as separate columns.

## Skipping unknown columns[​](#skipping-unknown-columns "Direct link to Skipping unknown columns")

By default, ClickHouse will ignore unknown columns when importing JSON data. Let's try to import the original file into the table without the `month` column:

```
CREATE TABLE shorttable
(
    `path` String,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY path
```

We can still insert the [original JSON data](/docs/assets/files/list-e590894c9dcb14bad20ba201c5806dca.json) with 3 columns into this table:

```
INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;
SELECT * FROM shorttable
```

```
┌─path──────────────────────┬─hits─┐
│ 1971-72_Utah_Stars_season │    1 │
│ Aegithina_tiphia          │   34 │
│ Akiba_Hebrew_Academy      │  241 │
└───────────────────────────┴──────┘
```

ClickHouse will ignore unknown columns while importing. This can be disabled with the [input\_format\_skip\_unknown\_fields](/docs/operations/settings/formats#input_format_skip_unknown_fields) settings option:

```
SET input_format_skip_unknown_fields = 0;
INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;
```

```
Ok.
Exception on client:
Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: month: (in file/uri /data/clickhouse/user_files/list.json): (at row 1)
```

ClickHouse will throw exceptions in cases of inconsistent JSON and table columns structure.

## BSON[​](#bson "Direct link to BSON")

ClickHouse allows exporting to and importing data from [BSON](https://bsonspec.org/) encoded files. This format is used by some DBMSs, e.g. [MongoDB](https://github.com/mongodb/mongo) database.

To import BSON data, we use the [BSONEachRow](/docs/interfaces/formats/BSONEachRow) format. Let's import data from [this BSON file](/docs/assets/files/data-fba81c8aea76b4e71c16bcb3b8a6a77a.bson):

```
SELECT * FROM file('data.bson', BSONEachRow)
```

```
┌─path──────────────────────┬─month─┬─hits─┐
│ Bob_Dolman                │ 17106 │  245 │
│ 1-krona                   │ 17167 │    4 │
│ Ahmadabad-e_Kalij-e_Sofla │ 17167 │    3 │
└───────────────────────────┴───────┴──────┘
```

We can also export to BSON files using the same format:

```
SELECT *
FROM sometable
INTO OUTFILE 'out.bson'
FORMAT BSONEachRow
```

After that, we'll have our data exported to the `out.bson` file.

- [Reading JSON as an object](#reading-json-as-an-object)- [Array of JSON objects](#array-of-json-objects)- [JSON object keys](#json-object-keys)
      - [Specifying parent object key values](#specifying-parent-object-key-values)- [JSON arrays](#json-arrays)
        - [Importing individual columns from JSON arrays](#importing-individual-columns-from-json-arrays)- [Saving JSON objects instead of parsing](#saving-json-objects-instead-of-parsing)- [Schema for nested objects](#schema-for-nested-objects)- [Accessing nested JSON objects](#accessing-nested-json-objects)- [Skipping unknown columns](#skipping-unknown-columns)- [BSON](#bson)

Was this page helpful?