Handling other JSON formats

Let's create a table for this kind of data:

```
CREATE TABLE sometable
(
    `path` String,
    `month` Date,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY tuple(month, path)
```

To import a list of JSON objects, we can use a [`JSONEachRow`](/docs/interfaces/formats/JSONEachRow) format (inserting data from [list.json](/docs/assets/files/list-e590894c9dcb14bad20ba201c5806dca.json) file):

```
INSERT INTO sometable
FROM INFILE 'list.json'
FORMAT JSONEachRow
```

We have used a [FROM INFILE](/docs/sql-reference/statements/insert-into#inserting-data-from-a-file) clause to load data from the local file, and we can see the import was successful:

```
SELECT *
FROM sometable
```

```
┌─path──────────────────────┬──────month─┬─hits─┐
│ 1971-72_Utah_Stars_season │ 2016-10-01 │    1 │
│ Akiba_Hebrew_Academy      │ 2017-08-01 │  241 │
│ Aegithina_tiphia          │ 2018-02-01 │   34 │
└───────────────────────────┴────────────┴──────┘
```

## JSON object keys[​](#json-object-keys "Direct link to JSON object keys")

In some cases, the list of JSON objects can be encoded as object properties instead of array elements (see [objects.json](/docs/assets/files/objects-c065aaa42144df69198a696f6811321b.json) for example):

```
cat objects.json
```

```
{
  "a": {
    "path":"April_25,_2017",
    "month":"2018-01-01",
    "hits":2
  },
  "b": {
    "path":"Akahori_Station",
    "month":"2016-06-01",
    "hits":11
  },
  ...
}
```

ClickHouse can load data from this kind of data using the [`JSONObjectEachRow`](/docs/interfaces/formats/JSONObjectEachRow) format:

```
INSERT INTO sometable FROM INFILE 'objects.json' FORMAT JSONObjectEachRow;
SELECT * FROM sometable;
```

```
┌─path────────────┬──────month─┬─hits─┐
│ Abducens_palsy  │ 2016-05-01 │   28 │
│ Akahori_Station │ 2016-06-01 │   11 │
│ April_25,_2017  │ 2018-01-01 │    2 │
└─────────────────┴────────────┴──────┘
```

### Specifying parent object key values[​](#specifying-parent-object-key-values "Direct link to Specifying parent object key values")

Let's say we also want to save values in parent object keys to the table. In this case, we can use the [following option](/docs/operations/settings/formats#format_json_object_each_row_column_for_object_name) to define the name of the column we want key values to be saved to:

```
SET format_json_object_each_row_column_for_object_name = 'id'
```

Now, we can check which data is going to be loaded from the original JSON file using [`file()`](/docs/sql-reference/functions/files#file) function:

```
SELECT * FROM file('objects.json', JSONObjectEachRow)
```

```
┌─id─┬─path────────────┬──────month─┬─hits─┐
│ a  │ April_25,_2017  │ 2018-01-01 │    2 │
│ b  │ Akahori_Station │ 2016-06-01 │   11 │
│ c  │ Abducens_palsy  │ 2016-05-01 │   28 │
└────┴─────────────────┴────────────┴──────┘
```

Note how the `id` column has been populated by key values correctly.

## JSON arrays[​](#json-arrays "Direct link to JSON arrays")

Sometimes, for the sake of saving space, JSON files are encoded in arrays instead of objects. In this case, we deal with a [list of JSON arrays](/docs/assets/files/arrays-312007dfdc6340fedc824785b471aab5.json):

```
cat arrays.json
```

```
["Akiba_Hebrew_Academy", "2017-08-01", 241],
["Aegithina_tiphia", "2018-02-01", 34],
["1971-72_Utah_Stars_season", "2016-10-01", 1]
```

In this case, ClickHouse will load this data and attribute each value to the corresponding column based on its order in the array. We use [`JSONCompactEachRow`](/docs/interfaces/formats/JSONCompactEachRow) format for this:

```
SELECT * FROM sometable
```

```
┌─c1────────────────────────┬─────────c2─┬──c3─┐
│ Akiba_Hebrew_Academy      │ 2017-08-01 │ 241 │
│ Aegithina_tiphia          │ 2018-02-01 │  34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │   1 │
└───────────────────────────┴────────────┴─────┘
```

### Importing individual columns from JSON arrays[​](#importing-individual-columns-from-json-arrays "Direct link to Importing individual columns from JSON arrays")

In some cases, data can be encoded column-wise instead of row-wise. In this case, a parent JSON object contains columns with values. Take a look at the [following file](/docs/assets/files/columns-b5f7725e736f41c9f065342a554e79cf.json):

```
cat columns.json
```

```
{
  "path": ["2007_Copa_America", "Car_dealerships_in_the_USA", "Dihydromyricetin_reductase"],
  "month": ["2016-07-01", "2015-07-01", "2015-07-01"],
  "hits": [178, 11, 1]
}
```

ClickHouse uses the [`JSONColumns`](/docs/interfaces/formats/JSONColumns) format to parse data formatted like that:

```
SELECT * FROM file('columns.json', JSONColumns)
```

```
┌─path───────────────────────┬──────month─┬─hits─┐
│ 2007_Copa_America          │ 2016-07-01 │  178 │
│ Car_dealerships_in_the_USA │ 2015-07-01 │   11 │
│ Dihydromyricetin_reductase │ 2015-07-01 │    1 │
└────────────────────────────┴────────────┴──────┘
```

A more compact format is also supported when dealing with an [array of columns](/docs/assets/files/columns-array-c8efc8e942324c6820ce841d68336180.json) instead of an object using [`JSONCompactColumns`](/docs/interfaces/formats/JSONCompactColumns) format:

```
SELECT * FROM file('columns-array.json', JSONCompactColumns)
```

```
┌─c1──────────────┬─────────c2─┬─c3─┐
│ Heidenrod       │ 2017-01-01 │ 10 │
│ Arthur_Henrique │ 2016-11-01 │ 12 │
│ Alan_Ebnother   │ 2015-11-01 │ 66 │
└─────────────────┴────────────┴────┘
```

## Saving JSON objects instead of parsing[​](#saving-json-objects-instead-of-parsing "Direct link to Saving JSON objects instead of parsing")

There are cases you might want to save JSON objects to a single `String` (or `JSON`) column instead of parsing it. This can be useful when dealing with a list of JSON objects of different structures. Let's take [this file](/docs/assets/files/custom-aad5c988b86a38da5e95f5d76aa438e6.json) for example, where we have multiple different JSON objects inside a parent list:

```
cat custom.json
```

```
[
  {"name": "Joe", "age": 99, "type": "person"},
  {"url": "/my.post.MD", "hits": 1263, "type": "post"},
  {"message": "Warning on disk usage", "type": "log"}
]
```

We want to save original JSON objects into the following table:

```
CREATE TABLE events
(
    `data` String
)
ENGINE = MergeTree
ORDER BY ()
```

Now we can load data from the file into this table using [`JSONAsString`](/docs/interfaces/formats/JSONAsString) format to keep JSON objects instead of parsing them:

```
INSERT INTO events (data)
FROM INFILE 'custom.json'
FORMAT JSONAsString
```

And we can use [JSON functions](/docs/sql-reference/functions/json-functions) to query saved objects:

```
SELECT
    JSONExtractString(data, 'type') AS type,
    data
FROM events
```

```
┌─type───┬─data─────────────────────────────────────────────────┐
│ person │ {"name": "Joe", "age": 99, "type": "person"}         │
│ post   │ {"url": "/my.post.MD", "hits": 1263, "type": "post"} │
│ log    │ {"message": "Warning on disk usage", "type": "log"}  │
└────────┴──────────────────────────────────────────────────────┘
```

Note that `JSONAsString` works perfectly fine in cases we have JSON object-per-line formatted files (usually used with `JSONEachRow` format).

## Schema for nested objects[​](#schema-for-nested-objects "Direct link to Schema for nested objects")

In cases when we're dealing with [nested JSON objects](/docs/assets/files/list-nested-ea2507b8a6fd7aabe23665516cf650e2.json), we can additionally define an explicit schema and use complex types ([`Array`](/docs/sql-reference/data-types/array), [`JSON`](/docs/integrations/data-formats/json/overview) or [`Tuple`](/docs/sql-reference/data-types/tuple)) to load data:

```
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")