mirror of
https://github.com/ClickHouse/ClickHouse.git
synced 2026-10-28 16:18:23 +08:00
95 lines
4.1 KiB
Plaintext
95 lines
4.1 KiB
Plaintext
---
|
|
alias: []
|
|
description: 'Documentation for the JSONCompactStringsEachRowWithNames format'
|
|
input_format: true
|
|
keywords: ['JSONCompactStringsEachRowWithNames']
|
|
output_format: true
|
|
slug: /interfaces/formats/JSONCompactStringsEachRowWithNames
|
|
title: 'JSONCompactStringsEachRowWithNames'
|
|
doc_type: 'reference'
|
|
---
|
|
|
|
{/*AUTOGENERATED_START*/}
|
|
| Input | Output | Alias |
|
|
|-------|--------|-------|
|
|
| ✔ | ✔ | |
|
|
|
|
## Description {#description}
|
|
|
|
Differs from the [`JSONCompactEachRow`](/reference/formats/JSON/JSONCompactEachRow) format in that it also prints the header row with column names, similar to the [TabSeparatedWithNames](/reference/formats/TabSeparated/TabSeparatedWithNames) format.
|
|
|
|
## Example usage {#example-usage}
|
|
|
|
### Inserting data {#inserting-data}
|
|
|
|
Using a JSON file with the following data, named as `football.json`:
|
|
|
|
```json
|
|
["date", "season", "home_team", "away_team", "home_team_goals", "away_team_goals"]
|
|
["2022-04-30", "2021", "Sutton United", "Bradford City", "1", "4"]
|
|
["2022-04-30", "2021", "Swindon Town", "Barrow", "2", "1"]
|
|
["2022-04-30", "2021", "Tranmere Rovers", "Oldham Athletic", "2", "0"]
|
|
["2022-05-02", "2021", "Port Vale", "Newport County", "1", "2"]
|
|
["2022-05-02", "2021", "Salford City", "Mansfield Town", "2", "2"]
|
|
["2022-05-07", "2021", "Barrow", "Northampton Town", "1", "3"]
|
|
["2022-05-07", "2021", "Bradford City", "Carlisle United", "2", "0"]
|
|
["2022-05-07", "2021", "Bristol Rovers", "Scunthorpe United", "7", "0"]
|
|
["2022-05-07", "2021", "Exeter City", "Port Vale", "0", "1"]
|
|
["2022-05-07", "2021", "Harrogate Town A.F.C.", "Sutton United", "0", "2"]
|
|
["2022-05-07", "2021", "Hartlepool United", "Colchester United", "0", "2"]
|
|
["2022-05-07", "2021", "Leyton Orient", "Tranmere Rovers", "0", "1"]
|
|
["2022-05-07", "2021", "Mansfield Town", "Forest Green Rovers", "2", "2"]
|
|
["2022-05-07", "2021", "Newport County", "Rochdale", "0", "2"]
|
|
["2022-05-07", "2021", "Oldham Athletic", "Crawley Town", "3", "3"]
|
|
["2022-05-07", "2021", "Stevenage Borough", "Salford City", "4", "2"]
|
|
["2022-05-07", "2021", "Walsall", "Swindon Town", "0", "3"]
|
|
```
|
|
|
|
Insert the data:
|
|
|
|
```sql
|
|
INSERT INTO football FROM INFILE 'football.json' FORMAT JSONCompactStringsEachRowWithNames;
|
|
```
|
|
|
|
### Reading data {#reading-data}
|
|
|
|
Read data using the `JSONCompactStringsEachRowWithNames` format:
|
|
|
|
```sql
|
|
SELECT *
|
|
FROM football
|
|
FORMAT JSONCompactStringsEachRowWithNames
|
|
```
|
|
|
|
The output will be in JSON format:
|
|
|
|
```json
|
|
["date", "season", "home_team", "away_team", "home_team_goals", "away_team_goals"]
|
|
["2022-04-30", "2021", "Sutton United", "Bradford City", "1", "4"]
|
|
["2022-04-30", "2021", "Swindon Town", "Barrow", "2", "1"]
|
|
["2022-04-30", "2021", "Tranmere Rovers", "Oldham Athletic", "2", "0"]
|
|
["2022-05-02", "2021", "Port Vale", "Newport County", "1", "2"]
|
|
["2022-05-02", "2021", "Salford City", "Mansfield Town", "2", "2"]
|
|
["2022-05-07", "2021", "Barrow", "Northampton Town", "1", "3"]
|
|
["2022-05-07", "2021", "Bradford City", "Carlisle United", "2", "0"]
|
|
["2022-05-07", "2021", "Bristol Rovers", "Scunthorpe United", "7", "0"]
|
|
["2022-05-07", "2021", "Exeter City", "Port Vale", "0", "1"]
|
|
["2022-05-07", "2021", "Harrogate Town A.F.C.", "Sutton United", "0", "2"]
|
|
["2022-05-07", "2021", "Hartlepool United", "Colchester United", "0", "2"]
|
|
["2022-05-07", "2021", "Leyton Orient", "Tranmere Rovers", "0", "1"]
|
|
["2022-05-07", "2021", "Mansfield Town", "Forest Green Rovers", "2", "2"]
|
|
["2022-05-07", "2021", "Newport County", "Rochdale", "0", "2"]
|
|
["2022-05-07", "2021", "Oldham Athletic", "Crawley Town", "3", "3"]
|
|
["2022-05-07", "2021", "Stevenage Borough", "Salford City", "4", "2"]
|
|
["2022-05-07", "2021", "Walsall", "Swindon Town", "0", "3"]
|
|
```
|
|
|
|
## Format settings {#format-settings}
|
|
|
|
<Note>
|
|
If setting [`input_format_with_names_use_header`](/reference/settings/formats/input-format#input_format_with_names_use_header) is set to `1`,
|
|
the columns from input data will be mapped to the columns from the table by their names, columns with unknown names will be skipped if setting [`input_format_skip_unknown_fields`](/reference/settings/formats/input-format#input_format_skip_unknown_fields) is set to `1`.
|
|
Otherwise, the first row will be skipped.
|
|
</Note>
|
|
{/*AUTOGENERATED_END*/}
|