TO_JSON_STRING in BigQuery: Alles, was Sie wissen müssen

TO_JSON_STRING ist eine BigQuery-Funktion, die einen SQL-Wert in einen JSON-formatierten STRING umwandelt. Sie rufen sie mit TO_JSON_STRING(value[, pretty_print]) auf, und BigQuery gibt den Wert als JSON-Text zurück, den Sie speichern, vergleichen, protokollieren oder an ein anderes System weitergeben können. Die Funktion arbeitet mit fast jedem BigQuery-Typ, darunter STRUCT, ein Datensatz mit benannten Feldern, und ARRAY, eine geordnete Liste von Werten. Sie verarbeitet auch verschachtelte Kombinationen von beiden. Deshalb nutzt man sie oft, um in einer einzigen QA-Prüfung ganze Zeilen zweier Tabellen zu vergleichen (die Technik für den Zeilenvergleich zeigt dieser LinkedIn-Beitrag).

TO_JSON_STRING: Syntax

TO_JSON_STRING(value[, pretty_print])

• value ist ein beliebiger BigQuery-Ausdruck: eine Spalte, ein STRUCT, ein ARRAY oder eine Kombination davon.

• pretty_print ist optional und steht standardmäßig auf FALSE. Setzen Sie es auf TRUE, erhalten Sie Zeilenumbrüche und Einrückungen statt einer einzigen kompakten Zeile.

BigQuery JSON zu String: Umwandeln mit TO_JSON_STRING

Der einfachste Fall ist ein Skalar oder ein Array:

SELECT TO_JSON_STRING([1, 2, 3]) AS json_string;

Ergebnis:

[1,2,3]

Ein STRUCT wird zu einem JSON-Objekt, wobei die Feldnamen des Structs als Schlüssel dienen:

SELECT TO_JSON_STRING(STRUCT('John' AS name, 30 AS age)) AS json_string;

Ergebnis:

{"name":"John","age":30}

Ausgabe formatiert ausgeben

Übergeben Sie TRUE als zweites Argument, um das Ergebnis lesbar statt platzsparend zu formatieren:

SELECT TO_JSON_STRING(STRUCT('John' AS name, 30 AS age), true) AS json_string;

Ergebnis:

{
  "name": "John",
  "age": 30
}

Eine ganze Zeile mit verschachtelten Arrays umwandeln

Dieses Muster macht TO_JSON_STRING über einen einzelnen Wert hinaus nützlich: Es macht aus einer Zeile und den zugehörigen untergeordneten Zeilen einen einzigen JSON-String. Die folgende Abfrage ist in sich geschlossen, Sie können sie unverändert in BigQuery ausführen:

WITH orders AS (
  SELECT 1 AS order_id, 'Ana Muller' AS customer_name, 'Keyboard' AS product_name, 2 AS quantity, 45 AS price
  UNION ALL
  SELECT 1, 'Ana Muller', 'Mouse', 1, 25
  UNION ALL
  SELECT 2, 'Jon Voss', 'Monitor', 1, 199
)
SELECT
  TO_JSON_STRING(
    STRUCT(
      order_id,
      customer_name,
      ARRAY_AGG(STRUCT(product_name, quantity, price) ORDER BY product_name) AS line_items
    )
  ) AS order_json
FROM orders
WHERE order_id = 1
GROUP BY order_id, customer_name;

Ergebnis:

{"order_id":1,"customer_name":"Ana Muller","line_items":[{"product_name":"Keyboard","quantity":2,"price":45},{"product_name":"Mouse","quantity":1,"price":25}]}

Das ORDER BY innerhalb von ARRAY_AGG ist wichtig. BigQuery garantiert die Zeilenreihenfolge in einem aggregierten Array nur, wenn Sie eine angeben. Ohne ORDER BY könnte sich die Reihenfolge von line_items zwischen Läufen ändern, obwohl sich die Daten nicht geändert haben.

Zwei Tabellen Zeile für Zeile mit TO_JSON_STRING vergleichen

TO_JSON_STRING macht aus einer ganzen Zeile einen String. Deshalb lassen sich zwei Zeilen mit einer einzigen Gleichheitsprüfung vergleichen statt mit einer Bedingung pro Spalte. Diese Abfrage prüft nach einem Ladevorgang eine Staging-Tabelle gegen die Produktion:

SELECT
  TO_JSON_STRING(prod) = TO_JSON_STRING(stg) AS is_column_match,
  COUNT(*) AS row_count
FROM (
  SELECT * EXCEPT (loaded_at)
  FROM analytics.metrics_daily
) prod
FULL JOIN (
  SELECT * EXCEPT (loaded_at)
  FROM staging.metrics_daily
) stg
  ON prod.id = stg.id
GROUP BY 1;

Eine einzelne Zeile mit true bedeutet, dass alle 100 Zeilen übereinstimmten.

Drei Details sorgen dafür, dass das funktioniert:

1. EXCEPT (loaded_at) lässt Spalten weg, die sich erwartbar unterscheiden, etwa Ladezeitstempel.

2. Beide Seiten müssen dieselben Spalten in derselben Reihenfolge haben, weil die JSON-Strings als Text verglichen werden.

3. Der FULL JOIN behält Zeilen, die nur auf einer Seite existieren. Bei diesen Zeilen ist ein Alias NULL, der Vergleich ist nicht true, und sie erscheinen in der Anzahl der Abweichungen.

TO_JSON_STRING vs. TO_JSON

Beide sind Funktionen in BigQuery, keine Operatoren. Der Unterschied liegt im Rückgabetyp:

• TO_JSON_STRING gibt einen STRING zurück. Nutzen Sie ihn, wenn Sie JSON als Text brauchen, etwa um es in eine STRING-Spalte zu schreiben, mit einem anderen String zu vergleichen oder zu exportieren.

• TO_JSON gibt einen Wert vom Datentyp JSON zurück. Nutzen Sie es, wenn Sie mit dem Ergebnis in BigQuery weiter als JSON arbeiten wollen. Der Typ JSON unterstützt Feldzugriff und die JSON-Funktionen direkt, ohne dass Sie zuerst einen String parsen müssen.

SELECT
  TO_JSON_STRING(STRUCT(1 AS a, 'x' AS b)) AS as_string,
  TO_JSON(STRUCT(1 AS a, 'x' AS b)) AS as_json;

Beide Spalten zeigen denselben Text, {"a":1,"b":"x"}, aber as_string ist eine STRING-Spalte und as_json eine JSON-Spalte. Nur as_json unterstützt einen Pfadausdruck wie as_json.a ohne zusätzlichen Parse-Schritt.

BigQuery String zu JSON: Umwandeln mit PARSE_JSON

In die Gegenrichtung, von einem JSON-formatierten String zu einem nutzbaren Wert, verwenden Sie PARSE_JSON und JSON_VALUE statt TO_JSON_STRING.

PARSE_JSON: vom String zum Typ JSON

SELECT PARSE_JSON('{"name":"John","age":30}') AS json_value;

Ergebnis: ein Wert vom Typ JSON statt eines STRING. Haben Sie einen JSON-Wert, können Sie ein Feld mit einem Pfadausdruck lesen:

SELECT PARSE_JSON('{"name":"John","age":30}').name AS name_value;

Ergebnis: "John" (der Feldzugriff auf einen JSON-Wert gibt weiter JSON zurück, der String kommt also in Anführungszeichen. Packen Sie ihn in JSON_VALUE, wenn Sie stattdessen einen einfachen STRING wollen).

JSON_VALUE: einen Skalar als STRING herausziehen

SELECT JSON_VALUE('{"name":"John","age":30}', '$.name') AS name_value;

Ergebnis: John, ein SQL-STRING ohne Anführungszeichen. JSON_VALUE funktioniert mit einem JSON-formatierten STRING und mit einer Spalte vom Typ JSON. Zeigt der Pfad auf ein Objekt oder ein Array statt auf einen Skalar, gibt es NULL zurück. Nutzen Sie diese Funktion, wenn Sie einen einfachen String zurückhaben wollen, denn sie entfernt die Anführungszeichen und löst Escapes für Sie auf.

Beispiele für TO_JSON_STRING im Überblick

1. TO_JSON_STRING([1, 2, 3]) für ein einfaches Array.

2. TO_JSON_STRING(STRUCT('John' AS name, 30 AS age)) für eine einzelne Zeile als flaches Objekt.

3. TO_JSON_STRING(STRUCT(order_id, customer_name, ARRAY_AGG(STRUCT(...)) AS line_items)) für eine Zeile, in der zugehörige untergeordnete Zeilen verschachtelt sind.

TO_JSON_STRING ist nützlich für QA-Zeilenvergleiche, um den vollständigen Zustand einer Zeile an einer Stelle der Pipeline zu protokollieren und um JSON für ein System außerhalb von BigQuery zu erzeugen.

FAQs

Was ist der Unterschied zwischen TO_JSON und TO_JSON_STRING in BigQuery?

TO_JSON_STRING gibt einen STRING mit JSON-Text zurück. TO_JSON gibt einen Wert vom Datentyp JSON zurück. Beide sind Funktionen. Nutzen Sie TO_JSON_STRING, wenn Sie JSON als Text brauchen. Nutzen Sie TO_JSON, wenn Sie das Ergebnis in BigQuery weiter als JSON abfragen wollen.

Wie wandle ich in BigQuery einen String in JSON um?

Mit PARSE_JSON machen Sie aus einem JSON-formatierten STRING einen Wert vom Typ JSON. Mit JSON_VALUE holen Sie nur ein skalares Feld aus diesem JSON als einfachen STRING heraus, statt den ganzen JSON-Wert.

Wie gebe ich JSON in BigQuery formatiert aus?

Übergeben Sie TRUE als zweites Argument an TO_JSON_STRING: TO_JSON_STRING(value, true). BigQuery formatiert das Ergebnis dann mit Zeilenumbrüchen und Einrückungen statt in einer kompakten Zeile.

Funktioniert TO_JSON_STRING mit einer ganzen Zeile, nicht nur mit einem Wert?

Ja. Packen Sie die gewünschten Spalten in ein STRUCT und übergeben Sie dieses STRUCT an TO_JSON_STRING. Zugehörige untergeordnete Zeilen aggregieren Sie zuerst mit ARRAY_AGG und STRUCT zu einem Array und verschachteln dieses Array dann im äußeren STRUCT, wie im Beispiel mit den Bestellungen oben.

Quellen

• JSON functions - Google Cloud BigQuery documentation