Skip to content

Comment convertir un tableau extrait d’un champ de chaîne json en un champ répété bigquery ?

Solution:

Il n’y a aucun moyen de le faire à l’aide de fonctions SQL dans BigQuery au moment de la rédaction de cet article, à moins que vous ne puissiez imposer une limite stricte au nombre de valeurs dans le tableau JSON ; voir l’élément de suivi des problèmes correspondant. Vos options sont :

  • Traitez les données différemment (par exemple, à l’aide de Cloud Dataflow ou d’un autre outil) afin de pouvoir les charger à partir de JSON délimité par une nouvelle ligne dans BigQuery.
  • Utilisez une UDF JavaScript qui prend le JSON d’entrée et renvoie le type souhaité ; c’est assez simple mais utilise généralement plus de CPU (et peut donc nécessiter un niveau de facturation plus élevé).
  • Utilisez les fonctions SQL en sachant que la solution échoue s’il y a trop d’éléments.

Voici l’approche utilisant une UDF JavaScript :

#standardSQL
CREATE TEMP FUNCTION JsonToItems(input STRING)
RETURNS STRUCT<order_id INT64, customer_id STRING, items ARRAY<STRUCT<line STRING, ref_ids ARRAY<STRING>, sku STRING, amount INT64>>>
LANGUAGE js AS """
return JSON.parse(input);
""";

WITH Input AS (
  SELECT '{"order_id":"123456","customer_id":"2abcd", "items":[{"line":"1","ref_ids":["66b56e60","9e7ca2b7"],"sku":"1111","amount":40 },{"line":"2","ref_ids":["7777h0","8888j0"],"sku":"2222","amount":10 }]}' AS json
)
SELECT
  JsonToItems(json).*
FROM Input;

Si vous souhaitez essayer l’approche basée sur SQL sans JavaScript, voici une sorte de piratage jusqu’à ce que la demande de fonctionnalité ci-dessus soit résolue, où le nombre d’éléments du tableau ne doit pas dépasser 10 :

#standardSQL
CREATE TEMP FUNCTION JsonExtractRefIds(json STRING) AS (
  (SELECT ARRAY_AGG(v IGNORE NULLS)
   FROM UNNEST([
     JSON_EXTRACT_SCALAR(json, '$.ref_ids[0]'),
     JSON_EXTRACT_SCALAR(json, '$.ref_ids[1]'),
     JSON_EXTRACT_SCALAR(json, '$.ref_ids[2]'),
     JSON_EXTRACT_SCALAR(json, '$.ref_ids[3]'),
     JSON_EXTRACT_SCALAR(json, '$.ref_ids[4]'),
     JSON_EXTRACT_SCALAR(json, '$.ref_ids[5]'),
     JSON_EXTRACT_SCALAR(json, '$.ref_ids[6]'),
     JSON_EXTRACT_SCALAR(json, '$.ref_ids[7]'),
     JSON_EXTRACT_SCALAR(json, '$.ref_ids[8]'),
     JSON_EXTRACT_SCALAR(json, '$.ref_ids[9]')]) AS v)
);

CREATE TEMP FUNCTION JsonToItem(json STRING)
RETURNS STRUCT<line STRING, ref_ids ARRAY<STRING>, sku STRING, amount INT64>
AS (
  IF(json IS NULL, NULL,
    STRUCT(
      JSON_EXTRACT_SCALAR(json, '$.line'),
      JsonExtractRefIds(json),
      JSON_EXTRACT_SCALAR(json, '$.sku'),
      CAST(JSON_EXTRACT_SCALAR(json, '$.amount') AS INT64)
    )
  )
);

CREATE TEMP FUNCTION JsonToItems(json STRING) AS (
  (SELECT AS STRUCT
    CAST(JSON_EXTRACT_SCALAR(json, '$.order_id') AS INT64) AS order_id,
    JSON_EXTRACT_SCALAR(json, '$.customer_id') AS customer_id,
    (SELECT ARRAY_AGG(v IGNORE NULLS)
     FROM UNNEST([
       JsonToItem(JSON_EXTRACT(json, '$.items[0]')),
       JsonToItem(JSON_EXTRACT(json, '$.items[1]')),
       JsonToItem(JSON_EXTRACT(json, '$.items[2]')),
       JsonToItem(JSON_EXTRACT(json, '$.items[3]')),
       JsonToItem(JSON_EXTRACT(json, '$.items[4]')),
       JsonToItem(JSON_EXTRACT(json, '$.items[5]')),
       JsonToItem(JSON_EXTRACT(json, '$.items[6]')),
       JsonToItem(JSON_EXTRACT(json, '$.items[7]')),
       JsonToItem(JSON_EXTRACT(json, '$.items[8]')),
       JsonToItem(JSON_EXTRACT(json, '$.items[9]'))]) AS v) AS items
  )
);

WITH Input AS (
  SELECT '{"order_id":"123456","customer_id":"2abcd", "items":[{"line":"1","ref_ids":["66b56e60","9e7ca2b7"],"sku":"1111","amount":40 },{"line":"2","ref_ids":["7777h0","8888j0"],"sku":"2222","amount":10 }]}' AS json
)
SELECT
  JsonToItems(json).*
FROM Input;

Version un peu plus brutale – je pense plus facile à lire et à modifier/ajuster si nécessaire

#standardSQL
WITH `yourTable` AS (
  SELECT '{"order_id":"123456","customer_id":"2abcd", "items":[{"line":"1","ref_ids":["66b56e60","9e7ca2b7"],"sku":"1111","amount":40 },{"line":"2","ref_ids":["7777h0","8888j0"],"sku":"2222","amount":10 }]}' AS json_blob
)
SELECT 
   JSON_EXTRACT_SCALAR(json_blob, '$.order_id') AS order_id,
   JSON_EXTRACT_SCALAR(json_blob, '$.customer_id') AS customer_id,
   ARRAY(
    SELECT STRUCT(
        JSON_EXTRACT_SCALAR(split_items, '$.line') AS line,
        SPLIT(REGEXP_REPLACE(JSON_EXTRACT (split_items, '$.ref_ids'), r'[[]"]', '')) AS ref_ids,
        JSON_EXTRACT_SCALAR(split_items, '$.sku') AS sku,
        JSON_EXTRACT_SCALAR(split_items, '$.amount') AS amount
      )
    FROM (
      SELECT CONCAT('{', REGEXP_REPLACE(split_items, r'^[{|}]$', ''), '}') AS split_items
      FROM UNNEST(SPLIT(JSON_EXTRACT(json_blob, '$.items'), '},{')) AS split_items
    )
   ) AS items
FROM `yourTable` 

Depuis le 1er mai 2020, la fonction JSON_EXTRACT_ARRAY a été ajoutée et peut être utilisée pour récupérer un tableau à partir de json.

#standardSQL
WITH `yourTable` AS (
  SELECT '{"order_id":"123456","customer_id":"2abcd", "items":[{"line":"1","ref_ids":["66b56e60","9e7ca2b7"],"sku":"1111","amount":40 },{"line":"2","ref_ids":["7777h0","8888j0"],"sku":"2222","amount":10 }]}' AS json_blob 
)
SELECT
  json_extract_scalar(json_blob,'$.order_id') AS order_id,
  json_extract_scalar(json_blob,'$.customer_id') AS customer_id,
  ARRAY(
  SELECT
    STRUCT(json_extract_scalar(split_items,'$.line') AS line,
          ARRAY(SELECT json_extract_scalar(ref_element,'$') FROM UNNEST(json_extract_array(split_items, '$.ref_ids')) ref_element) AS ref_ids,
          json_extract_scalar(split_items,'$.sku') AS sku,
          json_extract_scalar(split_items,'$.amount') AS amount 
      )
    FROM UNNEST(json_extract_array(json_blob,'$.items')) split_items 
  ) AS items
FROM
  `yourTable`

Retour:

entrez la description de l'image ici

Pour obtenir uniquement la requête de type serait :

#standardSQL
WITH `yourTable` AS (
  SELECT '{ "firstName": "John", "lastName" : "doe", "age"      : 26, "address"  : {     "streetAddress": "naist street",     "city"         : "Nara",     "postalCode"   : "630-0192" }, "phoneNumbers": [     {       "type"  : "iPhone",       "number": "0123-4567-8888"     },     {       "type"  : "home",       "number": "0123-4567-8910"     } ]}' AS json_blob 
)
  SELECT
    json_extract_scalar(split_items,'$.type') AS type FROM `yourTable`, UNNEST(json_extract_array(json_blob,'$.phoneNumbers')) split_items

Retour:

entrez la description de l'image ici



Articles Similaires

Groovy ComplexKeyHashMap.Entry

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.