Utiliser des données JSON en langage GoogleSQL

Ce document explique comment créer une table avec une colonne JSON, comment insérer des données JSON dans une table BigQuery, et comment interroger des données JSON.

BigQuery accepte de manière native les données JSON utilisant le type de données JSON.

JSON est un format couramment utilisé qui permet d'utiliser des données semi-structurées, car il ne nécessite pas de schéma. Les applications peuvent utiliser une approche de type "schéma sur lecture", dans laquelle l'application ingère les données, puis effectue des requêtes basées sur des hypothèses relatives au schéma de ces données. Cette approche diffère du type STRUCT dans BigQuery, qui nécessite un schéma fixe appliqué à toutes les valeurs stockées dans une colonne de type STRUCT.

En utilisant le type de données JSON, vous pouvez charger un code JSON semi-structuré dans BigQuery sans fournir de schéma pour les données JSON au préalable. Ainsi, vous pouvez stocker et interroger des données qui ne correspondent pas toujours à des schémas et des types de données fixes. En ingérant des données JSON en tant que type de données JSON, BigQuery peut encoder et traiter chaque champ JSON individuellement. Vous pouvez ensuite interroger les valeurs des champs et des éléments de tableau dans les données JSON à l'aide de l'opérateur d'accès aux champs, ce qui rend les requêtes JSON intuitives et améliore leur rentabilité.

Limites

  • Si vous utilisez un job de chargement par lot pour ingérer des données JSON dans une table, les données sources doivent être au format CSV, Avro ou JSON. Les autres formats de chargement par lot ne sont pas acceptés.
  • Le type de données JSON a une limite d'imbrication de 500.
  • Vous ne pouvez pas utiliser l'ancien langage SQL pour interroger une table contenant des types JSON.
  • Les règles d'accès au niveau des lignes ne peuvent pas être appliquées aux colonnes JSON.

Pour en savoir plus sur les propriétés du type de données JSON, consultez la section Type JSON.

Créer une table avec une colonne JSON

Vous pouvez créer une table vide avec une colonne JSON à l'aide de SQL ou de l'outil de ligne de commande bq.

SQL

Utilisez l'instruction CREATE TABLE et déclarez une colonne avec le type JSON.

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    CREATE TABLE mydataset.table1(
  id INT64,
  cart JSON
);

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

bq

Exécutez la commande bq mk, puis fournissez un schéma de table avec un type de données JSON.

bq mk --table mydataset.table1 id:INT64,cart:JSON

Vous ne pouvez pas partitionner ni mettre en cluster une table sur des colonnes JSON, car les opérateurs d'égalité et de comparaison ne sont pas définis sur le type JSON.

Créer des valeurs JSON

Vous pouvez créer des valeurs JSON de différentes manières :

  • Utilisez SQL pour créer un littéral JSON.
  • Utilisez la fonction PARSE_JSON pour convertir une valeur STRING en valeur JSON.
  • Utilisez la fonction TO_JSON pour convertir une valeur SQL en valeur JSON.
  • Utilisez la fonction JSON_ARRAY pour créer un tableau JSON à partir de valeurs SQL.
  • Utilisez la fonction JSON_OBJECT pour créer un objet JSON à partir de paires clé/valeur.

Créer une valeur JSON

L'exemple suivant insère des valeurs JSON dans une table :

INSERT INTO mydataset.table1 VALUES
(1, JSON '{"name": "Alice", "age": 30}'),
(2, JSON_ARRAY(10, ['foo', 'bar'], [20, 30])),
(3, JSON_OBJECT('foo', 10, 'bar', ['a', 'b']));

Convertir un type STRING en type JSON

L'exemple suivant convertit une valeur STRING au format JSON à l'aide de la fonction PARSE_JSON. L'exemple convertit une colonne d'une table existante au format JSON et stocke les résultats dans une nouvelle table.

CREATE OR REPLACE TABLE mydataset.table_new
AS (
  SELECT
    id, SAFE.PARSE_JSON(cart) AS cart_json
  FROM
    mydataset.old_table
);

Le préfixe SAFE utilisé dans cet exemple garantit que toutes les erreurs de conversion sont renvoyées sous forme de valeurs NULL.

Convertir des données schématisées au format JSON

L'exemple suivant convertit des paires clé/valeur au format JSON à l'aide de la fonction JSON_OBJECT.

WITH Fruits AS (
SELECT 0 AS id, 'color' AS k, 'Red' AS v UNION ALL
SELECT 0, 'fruit', 'apple' UNION ALL
SELECT 1, 'fruit','banana' UNION ALL
SELECT 1, 'ripe', 'true'
)

SELECT JSON_OBJECT(ARRAY_AGG(k), ARRAY_AGG(v)) AS json_data
FROM Fruits
GROUP BY id

Le résultat est le suivant :

+----------------------------------+
| json_data                        |
+----------------------------------+
| {"color":"Red","fruit":"apple"}  |
| {"fruit":"banana","ripe":"true"} |
+----------------------------------+

Convertir un type SQL en type JSON

L'exemple suivant convertit une valeur SQL STRUCT en valeur JSON à l'aide de la fonction TO_JSON :

SELECT TO_JSON(STRUCT(1 AS id, [10,20] AS coordinates)) AS pt;

Le résultat est le suivant :

+--------------------------------+
| pt                             |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
+--------------------------------+

Ingérer des données JSON

Vous pouvez ingérer des données JSON dans une table BigQuery de différentes manières :

Effectuer un chargement à partir de fichiers CSV

L'exemple suivant suppose que vous disposez d'un fichier CSV nommé file1.csv contenant les enregistrements suivants :

1,20
2,"""This is a string"""
3,"{""id"": 10, ""name"": ""Alice""}"

Notez que la deuxième colonne contient des données JSON encodées sous forme de chaîne. Cela implique d'échapper correctement les guillemets pour le format CSV, ce qui s'effectue à l'aide de la séquence de deux caractères "".

Pour charger ce fichier à l'aide de l'outil de ligne de commande bq, exécutez la commande bq load :

bq load --source_format=CSV mydataset.table1 file1.csv id:INTEGER,json_data:JSON

bq show mydataset.table1

Last modified          Schema         Total Rows   Total Bytes
----------------- -------------------- ------------ -------------
 22 Dec 22:10:32   |- id: integer       3            63
                   |- json_data: json

Effectuer un chargement à partir de fichiers JSON délimités par un retour à la ligne

L'exemple suivant suppose que vous disposez d'un fichier CSV nommé file1.jsonl contenant les enregistrements suivants :

{"id": 1, "json_data": 20}
{"id": 2, "json_data": "This is a string"}
{"id": 3, "json_data": {"id": 10, "name": "Alice"}}

Pour charger ce fichier à l'aide de l'outil de ligne de commande bq, exécutez la commande bq load :

bq load --source_format=NEWLINE_DELIMITED_JSON mydataset.table1 file1.jsonl id:INTEGER,json_data:JSON

bq show mydataset.table1

Last modified          Schema         Total Rows   Total Bytes
----------------- -------------------- ------------ -------------
 22 Dec 22:10:32   |- id: integer       3            63
                   |- json_data: json

Utiliser l'API Storage Write

Vous pouvez utiliser l'API Storage Write pour ingérer des données JSON. L'exemple suivant utilise le client Python de l'API Storage Write pour écrire des données dans une table avec une colonne de type de données JSON.

Définissez un tampon de protocole pour conserver les données en streaming sérialisées. Les données JSON sont encodées sous forme de chaîne. Dans l'exemple suivant, le champ json_col contient des données JSON.

message SampleData {
  optional string string_col = 1;
  optional int64 int64_col = 2;
  optional string json_col = 3;
}

Mettez en forme les données JSON pour chaque ligne en tant que valeur STRING :

row.json_col = '{"a": 10, "b": "bar"}'
row.json_col = '"This is a string"' # The double-quoted string is the JSON value.
row.json_col = '10'

Ajoutez les lignes au flux d'écriture, comme indiqué dans l'exemple de code. La bibliothèque cliente gère la sérialisation au format de tampon de protocole.

Si vous ne parvenez pas à mettre en forme les données JSON entrantes, vous devez utiliser la méthode json.dumps() dans votre code. Voici un exemple :

import json

...

row.json_col = json.dumps({"a": 10, "b": "bar"})
row.json_col = json.dumps("This is a string") # The double-quoted string is the JSON value.
row.json_col = json.dumps(10)

...

Utiliser l'ancienne API de diffusion de flux

L'exemple suivant charge des données JSON à partir d'un fichier local et les transmet à une table BigQuery avec une colonne de type de données JSON nommée json_data à l'aide de l'ancienne API de diffusion de flux.

from google.cloud import bigquery
import json

# TODO(developer): Replace these variables before running the sample.
project_id = 'MY_PROJECT_ID'
table_id = 'MY_TABLE_ID'

client = bigquery.Client(project=project_id)
table_obj = client.get_table(table_id)

# The column json_data is represented as a JSON data-type column.
rows_to_insert = [
    {"id": 1, "json_data": 20},
    {"id": 2, "json_data": "This is a string"},
    {"id": 3, "json_data": {"id": 10, "name": "Alice"}}
]

# If the column json_data is represented as a String data type, modify the rows_to_insert values:
#rows_to_insert = [
#    {"id": 1, "json_data": json.dumps(20)},
#    {"id": 2, "json_data": json.dumps("This is a string")},
#    {"id": 3, "json_data": json.dumps({"id": 10, "name": "Alice"})}
#]

# Throw errors if encountered.
# https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client#google_cloud_bigquery_client_Client_insert_rows

errors = client.insert_rows(table=table_obj, rows=rows_to_insert)
if errors == []:
    print("New rows have been added.")
else:
    print("Encountered errors while inserting rows: {}".format(errors))

Pour plus d'informations, consultez la page Insérer des données en flux continu dans BigQuery.

Interroger des données JSON

Cette section explique comment utiliser le langage GoogleSQL pour extraire des valeurs JSON. JSON est sensible à la casse et prend en charge UTF-8 dans les champs et les valeurs.

Les exemples de cette section utilisent la table suivante :

CREATE OR REPLACE TABLE mydataset.table1(id INT64, cart JSON);

INSERT INTO mydataset.table1 VALUES
(1, JSON """{
        "name": "Alice",
        "items": [
            {"product": "book", "price": 10},
            {"product": "food", "price": 5}
        ]
    }"""),
(2, JSON """{
        "name": "Bob",
        "items": [
            {"product": "pen", "price": 20}
        ]
    }""");

Extraire des valeurs au format JSON

Avec un type JSON dans BigQuery, vous pouvez accéder aux champs d'une expression JSON à l'aide de l'opérateur d'accès aux champs. L'exemple suivant renvoie le champ name de la colonne cart.

SELECT cart.name
FROM mydataset.table1;
 
+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

Pour accéder à un élément de tableau, utilisez l'opérateur d'indice JSON. L'exemple suivant renvoie le premier élément du tableau items :

SELECT
  cart.items[0] AS first_item
FROM mydataset.table1
 
+-------------------------------+
|          first_item           |
+-------------------------------+
| {"price":10,"product":"book"} |
| {"price":20,"product":"pen"}  |
+-------------------------------+

Vous pouvez également utiliser l'opérateur d'indice JSON pour référencer les membres d'un objet JSON par nom :

SELECT cart['name']
FROM mydataset.table1;
 
+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

Pour les opérations d'indice, l'expression entre crochets peut être n'importe quelle expression de chaîne ou d'entier arbitraire, y compris des expressions non constantes :

DECLARE int_val INT64 DEFAULT 0;

SELECT
  cart[CONCAT('it','ems')][int_val + 1].product AS item
FROM mydataset.table1;
 
+--------+
|  item  |
+--------+
| "food" |
| NULL   |
+--------+

Les opérateurs d'accès aux champs et d'indice renvoient des types JSON. Vous pouvez ainsi associer des expressions qui les utilisent ou transmettre le résultat à d'autres fonctions qui acceptent les types JSON.

Ces opérateurs sont des sucres syntaxiques pour la fonction JSON_QUERY. Par exemple, l'expression cart.name est équivalente à JSON_QUERY(cart, "$.name").

Si un membre portant le nom spécifié est introuvable dans l'objet JSON, ou si le tableau JSON ne comporte aucun élément avec la position spécifiée, ces opérateurs renvoient la valeur SQL NULL.

SELECT
  cart.address AS address,
  cart.items[1].price AS item1_price
FROM
  mydataset.table1;
 
+---------+-------------+
| address | item1_price |
+---------+-------------+
| NULL    | NULL        |
| NULL    | 5           |
+---------+-------------+

Les opérateurs d'égalité et de comparaison ne sont pas définis sur le type de données JSON. Par conséquent, vous ne pouvez pas utiliser directement de valeurs JSON dans des clauses telles que GROUP BY ou ORDER BY. Utilisez plutôt la fonction JSON_VALUE pour extraire les valeurs des champs en tant que chaînes SQL, comme décrit dans la section suivante.

Extraire des valeurs sous forme de chaînes

La fonction JSON_VALUE extrait une valeur scalaire et la renvoie sous forme de chaîne SQL. Elle renvoie SQL NULL si cart.name ne pointe pas vers une valeur scalaire dans le fichier JSON.

SELECT JSON_VALUE(cart.name) AS name
FROM mydataset.table1;
 
+-------+
| name  |
+-------+
| Alice |
+-------+

Vous pouvez utiliser la fonction JSON_VALUE dans les contextes qui nécessitent une égalité ou une comparaison, comme les clauses WHERE et GROUP BY. L'exemple suivant montre une clause WHERE qui filtre selon une valeur JSON :

SELECT
  cart.items[0] AS first_item
FROM
  mydataset.table1
WHERE
  JSON_VALUE(cart.name) = 'Alice';
 
+-------------------------------+
| first_item                    |
+-------------------------------+
| {"price":10,"product":"book"} |
+-------------------------------+

Vous pouvez également utiliser la fonction STRING, qui extrait une chaîne JSON et la renvoie sous forme de chaîne (STRING) SQL. Exemple :

SELECT STRING(JSON '"purple"') AS color;
 
+--------+
| color  |
+--------+
| purple |
+--------+

Outre STRING, vous devrez peut-être extraire des valeurs JSON et les renvoyer sous la forme d'un autre type de données SQL. Les fonctions d'extraction de valeur suivantes sont disponibles :

Pour obtenir le type de la valeur JSON, vous pouvez utiliser la fonction JSON_TYPE.

Convertir des valeurs JSON de manière flexible

Vous pouvez convertir une valeur JSON en valeur SQL scalaire de manière flexible et sans erreur avec les fonctions LAX Conversion.

L'exemple suivant démontre la puissance de ces fonctions. LAX_IN64 déduit et traite automatiquement l'entrée de manière appropriée. 

SELECT LAX_INT64(JSON '"10"') AS id;
 
+----+
| id |
+----+
| 10 |
+----+

En plus de LAX_IN64, vous pouvez convertir d'autres types SQL au format JSON de manière flexible en utilisant les fonctions suivantes :

Extraire des tableaux à partir de JSON

JSON peut contenir des tableaux JSON, qui ne sont pas directement équivalents à un type ARRAY<JSON> dans BigQuery. Vous pouvez utiliser les fonctions suivantes pour extraire un ARRAY BigQuery à partir de JSON :

  • JSON_QUERY_ARRAY : extrait un tableau et le renvoie sous forme d'un tableau (ARRAY<JSON>) JSON.
  • JSON_VALUE_ARRAY : extrait un tableau de valeurs scalaires et le renvoie sous forme d'un tableau (ARRAY<STRING>) de valeurs scalaires.

L'exemple suivant utilise JSON_QUERY_ARRAY pour extraire des tableaux JSON.

SELECT JSON_QUERY_ARRAY(cart.items) AS items
FROM mydataset.table1;
 
+----------------------------------------------------------------+
| items                                                          |
+----------------------------------------------------------------+
| [{"price":10,"product":"book"}","{"price":5,"product":"food"}] |
| [{"price":20,"product":"pen"}]                                 |
+----------------------------------------------------------------+

Pour diviser un tableau en éléments individuels, utilisez l'opérateur UNNEST, qui renvoie une table avec une ligne pour chaque élément du tableau. L'exemple suivant sélectionne le membre product pour chaque membre du tableau items :

SELECT
  id,
  JSON_VALUE(item.product) AS product
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
ORDER BY id;
 
+----+---------+
| id | product |
+----+---------+
|  1 | book    |
|  1 | food    |
|  2 | pen     |
+----+---------+

L'exemple suivant est similaire mais utilise la fonction ARRAY_AGG pour agréger les valeurs dans un tableau SQL.

SELECT
  id,
  ARRAY_AGG(JSON_VALUE(item.product)) AS products
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
GROUP BY id
ORDER BY id;
 
+----+-----------------+
| id | products        |
+----+-----------------+
|  1 | ["book","food"] |
|  2 | ["pen"]         |
+----+-----------------+

Pour en savoir plus sur les tableaux, consultez la page Utiliser des tableaux en langage GoogleSQL.

Valeurs JSON nulles

Le type JSON possède une valeur null spéciale différente de la valeur JSON NULL. Une valeur null n'est pas traitée comme une valeur SQL NULL, comme le montre l'exemple suivant.

SELECT JSON 'null' IS NULL;
 
+-------+
| f0_   |
+-------+
| false |
+-------+

Lorsque vous extrayez un champ JSON avec une valeur null, le comportement dépend de la fonction :

  • La fonction JSON_QUERY renvoie la valeur JSON null, car il s'agit d'une valeur JSON valide.
  • La fonction JSON_VALUE renvoie la valeur SQL NULL, car la valeur JSON null n'est pas une valeur scalaire.

L'exemple suivant illustre les différents comportements :

SELECT
  json.a AS json_query, -- Equivalent to JSON_QUERY(json, '$.a')
  JSON_VALUE(json, '$.a') AS json_value
FROM (SELECT JSON '{"a": null}' AS json);
 
+------------+------------+
| json_query | json_value |
+------------+------------+
| null       | NULL       |
+------------+------------+