JSONObjectEachRow

Dans ce format, toutes les données sont représentées dans un unique objet JSON, chaque ligne correspondant à un champ distinct de cet objet, à l'instar du format JSONEachRow.

Soit le JSON suivant :

{
  "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]}
}

Pour utiliser un nom d'objet comme valeur de colonne, vous pouvez utiliser le paramètre spécial format_json_object_each_row_column_for_object_name. La valeur de ce paramètre correspond au nom d'une colonne, utilisée comme clé JSON pour une ligne dans l'objet résultant.

Supposons que nous ayons la table test avec deux colonnes :

┌─object_name─┬─number─┐
│ first_obj   │      1 │
│ second_obj  │      2 │
│ third_obj   │      3 │
└─────────────┴────────┘

Affichons-le au format JSONObjectEachRow et utilisons le paramètre format_json_object_each_row_column_for_object_name :

SELECT * FROM test SETTINGS format_json_object_each_row_column_for_object_name='object_name'

{
    "first_obj": {"number": 1},
    "second_obj": {"number": 2},
    "third_obj": {"number": 3}
}

Supposons que nous ayons enregistré le résultat de l’exemple précédent dans un fichier nommé data.json :

SELECT * FROM file('data.json', JSONObjectEachRow, 'object_name String, number UInt64') SETTINGS format_json_object_each_row_column_for_object_name='object_name'

┌─object_name─┬─number─┐
│ first_obj   │      1 │
│ second_obj  │      2 │
│ third_obj   │      3 │
└─────────────┴────────┘

Cela fonctionne aussi pour l’inférence de schéma :

DESCRIBE file('data.json', JSONObjectEachRow) SETTING format_json_object_each_row_column_for_object_name='object_name'

┌─name────────┬─type────────────┐
│ object_name │ String          │
│ number      │ Nullable(Int64) │
└─────────────┴─────────────────┘

INSERT INTO UserActivity FORMAT JSONEachRow {"PageViews":5, "UserID":"4324182021466249494", "Duration":146,"Sign":-1} {"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}

ClickHouse autorise :

• N’importe quel ordre pour les paires clé-valeur dans l’objet.
• L’omission de certaines valeurs.

ClickHouse ignore les espaces entre les éléments ainsi que les virgules après les objets. Vous pouvez transmettre tous les objets sur une seule ligne. Il n’est pas nécessaire de les séparer par des sauts de ligne.

ClickHouse remplace les valeurs omises par les valeurs par défaut des types de données correspondants.

Si DEFAULT expr est spécifié, ClickHouse applique des règles de substitution différentes selon le paramètre input_format_defaults_for_omitted_fields.

Considérez le tableau suivant :

CREATE TABLE IF NOT EXISTS example_table
(
    x UInt32,
    a DEFAULT x * 2
) ENGINE = Memory;

• Si input_format_defaults_for_omitted_fields = 0, la valeur par défaut de x et de a est 0 (qui est la valeur par défaut du type de données UInt32).
• Si input_format_defaults_for_omitted_fields = 1, la valeur par défaut de x est 0, mais celle de a est x * 2.

Prenons la table UserActivity comme exemple :

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

La requête SELECT * FROM UserActivity FORMAT JSONEachRow renvoie :

{"UserID":"4324182021466249494","PageViews":5,"Duration":146,"Sign":-1}
{"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}

Contrairement au format JSON, les séquences UTF-8 non valides ne sont pas remplacées. Les valeurs sont échappées de la même manière qu’en JSON.

Si vous avez une table avec des colonnes du type de données Nested, vous pouvez insérer des données JSON ayant la même structure. Activez cette fonctionnalité avec le paramètre input_format_import_nested_json.

Par exemple, prenons la table suivante :

CREATE TABLE json_each_row_nested (n Nested (s String, i Int32) ) ENGINE = Memory

Comme vous pouvez le voir dans la description du type de données Nested, ClickHouse traite chaque composant de la structure imbriquée comme une colonne distincte (n.s et n.i dans notre table). Vous pouvez insérer les données de la manière suivante :

INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n.s": ["abc", "def"], "n.i": [1, 23]}

Pour insérer des données sous forme d’objet JSON hiérarchique, définissez input_format_import_nested_json=1.

{
    "n": {
        "s": ["abc", "def"],
        "i": [1, 23]
    }
}

Sans ce paramètre, ClickHouse lève une exception.

SELECT name, value FROM system.settings WHERE name = 'input_format_import_nested_json'

┌─name────────────────────────────┬─value─┐
│ input_format_import_nested_json │ 0     │
└─────────────────────────────────┴───────┘

INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}

Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: n: (at row 1)

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

┌─n.s───────────┬─n.i────┐
│ ['abc','def'] │ [1,23] │
└───────────────┴────────┘