Utilisez le type JSON pour stocker des données semi-structurées dans MaxCompute. Lors de l'écriture, MaxCompute extrait automatiquement un schéma public et stocke ces champs au format colonne. Lors de la lecture, l'élagage des colonnes analyse uniquement les champs sollicités par votre requête, offrant ainsi de meilleures performances et un stockage réduit par rapport au type STRING.
Quand utiliser le type JSON
|
|
|
|
|
|
|
|
|
|
|
|
Fonctionnement
Lorsque vous insérez des données JSON, MaxCompute extrait automatiquement un schéma public à partir des données et applique des optimisations. Les champs du schéma public sont stockés au format colonne. Les champs absents du schéma public sont stockés au format BINARY.
Par exemple, considérons trois lignes contenant les champs a, b et c :
INSERT INTO json_table
SELECT json_parse(string_val)
FROM string_table;MaxCompute extrait le schéma public <"a":binary, "b":bigint, "c":bigint>. Une requête ultérieure qui lit uniquement b et c n'analyse que ces colonnes :
SELECT json_val["b"], json_val["c"]
FROM json_table;
-- Column pruning keeps only b and c.
+------+------+
| _c0 | _c1 |
+------+------+
| 2 | NULL |
| 2 | NULL |
| NULL | 3 |
+------+------+Les valeurs JSON sont stockées à l'aide des types internes de MaxCompute. En raison de ce mappage, les valeurs situées en dehors de la plage BIGINT ou DOUBLE peuvent provoquer un dépassement de capacité ou une perte de précision.
Prérequis
Avant de commencer, assurez-vous de disposer des éléments suivants :
Un projet MaxCompute avec le type JSON activé (voir Activer le type JSON)
Java SDK V0.44.0 ou version ultérieure, ou PyODPS V0.11.4.1 ou version ultérieure
Si vous utilisez odpscmd : version V0.46.5 ou ultérieure, avec
use_instance_tunnel=falsedansconf\odps_config.ini
Activer le type JSON
Le paramètre odps.sql.type.json.enable contrôle la disponibilité du type JSON :
|
|
|
|
|
|
|
|
|
Pour activer le type JSON dans un projet existant :
SET odps.sql.type.json.enable=true;Pour vérifier la valeur actuelle :
setproject;Limitations
Référence des contraintes
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Stockage des types et précision
Les valeurs JSON sont mappées vers des types internes MaxCompute. Les valeurs situées en dehors de ces plages provoquent un dépassement de capacité ou une perte de précision :
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Exigences relatives aux outils et SDK
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Utiliser le type JSON
Tous les exemples ci-dessous utilisent un enregistrement de commande partagé pour illustrer de bout en bout les modèles de création, d'insertion et de requête :
{"id": 1001, "customer": "Molly", "amount": 299.50}Créer une table JSON
Aucune définition de schéma n'est requise — déclarez simplement la colonne comme JSON :
CREATE TABLE orders (record JSON);Générer des données JSON
À partir d'un littéral JSON :
INSERT INTO orders VALUES (JSON '{"id": 1001, "customer": "Molly", "amount": 299.50}');En utilisant JSON_OBJECT et JSON_ARRAY :
-- JSON_OBJECT builds a JSON object from key-value pairs.
INSERT INTO orders SELECT JSON_OBJECT("id", 1002, "customer", "Frank", "amount", 150.00);
-- JSON_ARRAY builds a JSON array.
SELECT JSON_ARRAY("tag1", "tag2", "promo");
-- Returns: ["tag1","tag2","promo"]En convertissant une colonne STRING :
Utilisez json_parse pour convertir les données chaîne existantes. Combinez-la avec json_valid pour ignorer les lignes mal formées :
INSERT INTO orders
SELECT json_parse(raw_json)
FROM staging_table
WHERE json_valid(raw_json);CAST("abc" AS JSON)etjson_parse("abc")se comportent différemment dans les cas limites. Consultez Fonctions JSON pour plus de détails.
Accéder aux données JSON
Tous les exemples ci-dessous interrogent la ligne insérée précédemment : {"id": 1001, "customer": "Molly", "amount": 299.50}.
Accès par index
L'accès par index utilise le mode strict : NULL est renvoyé lorsque le chemin ne correspond pas à la structure des données.
-- Returns 1001
SELECT record['id']
FROM orders
WHERE record['id'] IS NOT NULL;
-- Returns "Molly"
SELECT record['customer']
FROM orders;
-- Returns NULL (field does not exist)
SELECT record['email']
FROM orders;L'accès par index est équivalent à JSON_EXTRACT en mode strict :
-- These two expressions return the same result:
SELECT record['id'] FROM orders;
SELECT JSON_EXTRACT(record, 'strict $.id') FROM orders;
-- Both return: 1001Accès à l'aide des fonctions JSON
Deux fonctions sont disponibles :
|
|
|
|
|
|
|
|
|
|
|
|
Utilisez JSON_EXTRACT dans les nouvelles requêtes SQL : son analyseur est cohérent avec l'accesseur par index et prend en charge l'élagage des colonnes en mode strict.
-- JSON_EXTRACT returns a JSON value (with quotes).
SELECT JSON_EXTRACT(record, '$.customer')
FROM orders;
-- Returns: "Molly"
-- GET_JSON_OBJECT returns a STRING value (no quotes).
SELECT GET_JSON_OBJECT(record, '$.customer')
FROM orders;
-- Returns: MollyRéférence des chemins JSON
Un chemin JSON identifie un nœud dans les données JSON. L'analyseur utilisé par le type JSON est un sous-ensemble de la spécification JSON Path de PostgreSQL.
Données d'exemple pour les exemples de chemin JSON :
{
"name": "Molly",
"phones": [
{ "phonetype": "work", "phone#": "650-506-7000" },
{ "phonetype": "cell", "phone#": "650-555-5555" }
]
}Syntaxe de l'accesseur :
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Modes :
JSON Path prend en charge deux modes. Le mode par défaut est lax.
|
|
|
|
|
|
|
|
|
|
|
|
Exemples en mode lax (en utilisant les données d'exemple ci-dessus) :
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Exemples en mode strict :
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Utilisez le mode strict lorsque vous avez besoin de l'élagage des colonnes. Le mode lax ne prend pas en charge l'optimisation par élagage des colonnes.
Considérations de conception
Utilisez le mode strict pour l'élagage des colonnes. Le mode lax ne déclenche pas l'optimisation par élagage des colonnes, donc les requêtes en mode lax analysent davantage de données.
Maintenez les documents JSON de petite taille. Chaque colonne JSON est stockée comme une seule valeur de colonne. Les documents volumineux augmentent le coût mémoire et E/S par ligne.
Validez avant l'ingestion. Utilisez
json_valid()pour filtrer les lignes mal formées avant d'appelerjson_parse().Vérifiez la précision avant d'insérer de grands nombres. Les nombres JSON sont stockés sous forme de BIGINT (partie entière) et DOUBLE (partie décimale). Les nombres en dehors de ces plages provoquent un dépassement.
Planifiez votre schéma avant de créer les tables. Vous ne pouvez pas ajouter de colonne JSON à une table existante. Concevez la table avec la colonne JSON dès le départ.
Exemple de bout en bout
-- Enable the JSON type if your project was created before the feature was enabled.
SET odps.sql.type.json.enable=true;
-- Create a JSON table.
CREATE TABLE orders (record JSON);
-- Ingest from a staging STRING table, skipping malformed rows.
CREATE TABLE staging (raw_json STRING);
INSERT INTO staging VALUES ('{"id": 1001, "customer": "Molly", "amount": 299.50}');
INSERT INTO orders
SELECT json_parse(raw_json)
FROM staging
WHERE json_valid(raw_json);
-- Query all non-null records.
SELECT * FROM orders WHERE record IS NOT NULL;
-- Returns:
-- +--------------------------------------------------+
-- | record |
-- +--------------------------------------------------+
-- | {"id":1001,"customer":"Molly","amount":299.5} |
-- +--------------------------------------------------+
-- Access a specific field.
SELECT record['customer'] FROM orders WHERE record IS NOT NULL;
-- Returns:
-- +-----------+
-- | _c0 |
-- +-----------+
-- | "Molly" |
-- +-----------+Étapes suivantes
Fonctions JSON — référence complète pour
JSON_EXTRACT,GET_JSON_OBJECT,JSON_OBJECT,JSON_ARRAY,json_parse,json_validet les fonctions associéesExpressions CAST — comportement de conversion de type pour JSON