Cómo convertir un array extraído de un json string campo a un campo repetido de bigquery?

Intenta entender el código de forma correcta previamente a usarlo a tu proyecto si tdeseas aportar algo puedes compartirlo con nosotros.

Solución:

No hay forma de hacer esto usando funciones SQL en BigQuery en el momento de escribir este artículo, a menos que pueda imponer un límite estricto en la cantidad de valores en JSON. array; consulte el elemento de seguimiento de problemas correspondiente. Tus opciones son:

• Procese los datos de manera diferente (p. Ej., Con Cloud Dataflow u otra herramienta) para poder cargarlos desde JSON delimitado por saltos de línea en BigQuery.
• Utilice una UDF de JavaScript que tome la entrada JSON y devuelva el tipo deseado; esto es bastante sencillo pero generalmente usa más CPU (y por lo tanto puede requerir un nivel de facturación más alto).
• Utilice funciones SQL con el entendimiento de que la solución se descompone si hay demasiados elementos.

Este es el enfoque que utiliza una UDF de JavaScript:

#standardSQL
CREATE TEMP FUNCTION JsonToItems(input STRING)
RETURNS STRUCT, 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 desea probar el enfoque basado en SQL sin JavaScript, aquí hay una especie de truco hasta que se resuelva la solicitud de función anterior, donde el número de array los elementos no deben ser más de 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, 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;

Versión un poco más de fuerza bruta: creo que es más fácil de leer y modificar / ajustar si es necesario

#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` 

A partir del 1 de mayo de 2020, se agregó la función JSON_EXTRACT_ARRAY y se puede usar para recuperar array 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`

Devoluciones:

Para obtener solo el tipo de consulta sería:

#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

devoluciones:

valoraciones y comentarios

Puedes respaldar nuestra función añadiendo un comentario o dejando una puntuación te damos las gracias.

---

---

Utiliza Nuestro Buscador