JSON-Funktionen

BigQuery unterstützt die folgenden Funktionen, die JSON-Daten abrufen und transformieren können.

Funktionsübersicht

Die folgenden Funktionen verwenden doppelte Anführungszeichen, um ungültige JSONPath-Zeichen zu maskieren: "a.b".

Dieses Verhalten entspricht dem ANSI-Standard.

JSON-Funktion Beschreibung Rückgabetyp
JSON_QUERY Extrahiert einen JSON-Wert, z. B. ein Array oder Objekt oder einen skalaren JSON-formatierten Wert, wie einen String, eine Zahl oder einen booleschen Wert. JSON-formatiertes STRING oder JSON
JSON_VALUE Extrahiert einen skalaren Wert. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Entfernt die äußeren Anführungszeichen und maskiert die Werte. Gibt einen SQL-NULL zurück, wenn ein nicht-skalarer Wert ausgewählt ist. STRING
JSON_QUERY_ARRAY Extrahiert ein Array von JSON-Werten, wie Arrays oder Objekte, und skalare Werte im JSON-Format, wie Strings, Zahlen und boolesche Werte. ARRAY<JSON-formatted STRING> oder ARRAY<JSON>
JSON_VALUE_ARRAY Extrahiert ein Array von skalaren Werten. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Entfernt die äußeren Anführungszeichen und maskiert die Werte. Gibt einen SQL-NULL zurück, wenn der ausgewählte Wert kein Array oder kein Array mit skalaren Werten ist. ARRAY<STRING>

Legacy-JSON-Extraktionsfunktionen

Die folgenden Funktionen verwenden einfache Anführungszeichen und Klammern, um ungültige JSONPath-Zeichen zu maskieren: ['a.b'].

Obwohl diese Funktionen von BigQuery unterstützt werden, empfehlen wir die Verwendung der Funktionen aus der vorherigen Tabelle.

JSON-Funktion Beschreibung Rückgabetyp
JSON_EXTRACT Extrahiert einen JSON-Wert, z. B. ein Array oder Objekt oder einen skalaren JSON-formatierten Wert, wie einen String, eine Zahl oder einen booleschen Wert. JSON-formatiertes STRING oder JSON
JSON_EXTRACT_SCALAR Extrahiert einen skalaren Wert. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Entfernt die äußeren Anführungszeichen und maskiert die Werte. Gibt einen SQL-NULL zurück, wenn ein nicht-skalarer Wert ausgewählt ist. STRING
JSON_EXTRACT_ARRAY Extrahiert ein Array von JSON-Werten, wie Arrays oder Objekte, und skalare Werte im JSON-Format, wie Strings, Zahlen und boolesche Werte. ARRAY<JSON-formatted STRING> oder ARRAY<JSON>
JSON_EXTRACT_STRING_ARRAY Extrahiert ein Array von skalaren Werten. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Entfernt die äußeren Anführungszeichen und maskiert die Werte. Gibt einen SQL-NULL zurück, wenn der ausgewählte Wert kein Array oder kein Array mit skalaren Werten ist. ARRAY<STRING>

Weitere JSON-Funktionen

JSON-Funktion Beschreibung Rückgabetyp
PARSE_JSON Gibt für einen JSON-formatierten String einen JSON-Wert zurück. JSON
TO_JSON Gibt für einen SQL-Wert einen JSON-Wert zurück. JSON
TO_JSON_STRING Nimmt einen SQL-Wert an und gibt eine JSON-formatierte Stringdarstellung des Werts zurück. JSON-formatierte STRING
STRING Extrahiert einen String aus JSON. STRING
BOOL Extrahiert einen booleschen Wert aus JSON. BOOL
INT64 Extrahiert eine 64-Bit-Ganzzahl aus JSON. INT64
FLOAT64 Extrahiert eine 64-Bit-Gleitkommazahl aus JSON. FLOAT64
JSON_TYPE Gibt den Typ des äußeren JSON-Werts als String zurück. STRING

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)
JSON_EXTRACT(json_expr, json_path)

Beschreibung

Extrahiert einen JSON-Wert, z. B. ein Array oder Objekt oder einen skalaren JSON-formatierten Wert, wie einen String, eine Zahl oder einen booleschen Wert. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit einfachen Anführungszeichen und Klammern maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    

    Extrahiert einen SQL-NULL, wenn ein JSON-formatierter String null gefunden wird. Beispiel:

    SELECT JSON_EXTRACT("null", "$") -- Returns a SQL NULL
    
  • json_expr: JSON. Beispiel:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    

    Extrahiert einen JSON-null, wenn ein JSON-null gefunden wird.

    SELECT JSON_EXTRACT(JSON 'null', "$") -- Returns a JSON 'null'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten.

Rückgabetyp

  • json_string_expr: Ein JSON-formatierter STRING
  • json_expr: JSON

Beispiele

Im folgenden Beispiel werden JSON-Daten extrahiert und als JSON zurückgegeben.

SELECT
  JSON_EXTRACT(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

In den folgenden Beispielen werden JSON-Daten extrahiert und als JSON-formatierte Strings zurückgegeben.

SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+
SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_EXTRACT('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_EXTRACT('{"a":null}', "$.b"); -- Returns a SQL NULL
SELECT JSON_EXTRACT(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_EXTRACT(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)
JSON_QUERY(json_expr, json_path)

Beschreibung

Extrahiert einen JSON-Wert, z. B. ein Array oder Objekt oder einen skalaren JSON-formatierten Wert, wie einen String, eine Zahl oder einen booleschen Wert. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    

    Extrahiert einen SQL-NULL, wenn ein JSON-formatierter String null gefunden wird. Beispiel:

    SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
    
  • json_expr: JSON. Beispiel:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    

    Extrahiert einen JSON-null, wenn ein JSON-null gefunden wird.

    SELECT JSON_QUERY(JSON 'null', "$") -- Returns a JSON 'null'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten.

Rückgabetyp

  • json_string_expr: Ein JSON-formatierter STRING
  • json_expr: JSON

Beispiele

Im folgenden Beispiel werden JSON-Daten extrahiert und als JSON zurückgegeben.

SELECT
  JSON_QUERY(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

In den folgenden Beispielen werden JSON-Daten extrahiert und als JSON-formatierte Strings zurückgegeben.

SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_QUERY('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL
SELECT JSON_QUERY(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_QUERY(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])
JSON_EXTRACT_SCALAR(json_expr[, json_path])

Beschreibung

Extrahiert einen skalaren Wert und gibt ihn dann als String zurück. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Entfernt die äußeren Anführungszeichen und maskiert die Rückgabewerte. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit einfachen Anführungszeichen und Klammern maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Beispiel:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h., dass alle Daten analysiert werden.

    Wenn json_path einen JSON-null oder einen nicht skalaren Wert zurückgibt (d. h., wenn json_path auf ein Objekt oder ein Array verweist), wird ein NULL-SQL zurückgegeben.

Rückgabetyp

STRING

Beispiele

Im folgenden Beispiel wird age extrahiert.

SELECT JSON_EXTRACT_SCALAR(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

Im folgenden Beispiel wird verglichen, wie Ergebnisse für die Funktionen JSON_EXTRACT und JSON_EXTRACT_SCALAR zurückgegeben werden.

SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') AS json_name,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') AS scalar_name,
  JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') AS json_age,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+-----------+-------------+----------+------------+
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob"   | Jakob       | "6"      | 6          |
+-----------+-------------+----------+------------+
SELECT JSON_EXTRACT('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract,
  JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract_scalar;

+--------------------+---------------------+
| json_extract       | json_extract_scalar |
+--------------------+---------------------+
| ["apple","banana"] | NULL                |
+--------------------+---------------------+

In Fällen, in denen ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit einfachen Anführungszeichen und Klammern wie [' '] maskieren. Beispiel:

SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") AS hello;

+-------+
| hello |
+-------+
| world |
+-------+

JSON_VALUE

JSON_VALUE(json_string_expr[, json_path])
JSON_VALUE(json_expr[, json_path])

Beschreibung

Extrahiert einen skalaren Wert und gibt ihn dann als String zurück. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Entfernt die äußeren Anführungszeichen und maskiert die Rückgabewerte. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Beispiel:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h., dass alle Daten analysiert werden.

    Wenn json_path einen JSON-null oder einen nicht skalaren Wert zurückgibt (d. h., wenn json_path auf ein Objekt oder ein Array verweist), wird ein NULL-SQL zurückgegeben.

Rückgabetyp

STRING

Beispiele

Im folgenden Beispiel werden JSON-Daten extrahiert und als skalarer Wert zurückgegeben.

SELECT JSON_VALUE(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

Im folgenden Beispiel wird verglichen, wie Ergebnisse für die Funktionen JSON_QUERY und JSON_VALUE zurückgegeben werden.

SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') AS json_name,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') AS scalar_name,
  JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') AS json_age,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+-----------+-------------+----------+------------+
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob"   | Jakob       | "6"      | 6          |
+-----------+-------------+----------+------------+
SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
  JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;

+--------------------+------------+
| json_query         | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL       |
+--------------------+------------+

Falls ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren. Beispiel:

SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') AS hello;

+-------+
| hello |
+-------+
| world |
+-------+

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path])
JSON_EXTRACT_ARRAY(json_expr[, json_path])

Beschreibung

Extrahiert ein Array von JSON-Werten, wie Arrays oder Objekte, und skalare Werte im JSON-Format, wie Strings, Zahlen und boolesche Werte. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit einfachen Anführungszeichen und Klammern maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Beispiel:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h., dass alle Daten analysiert werden.

Rückgabetyp

  • json_string_expr: ARRAY<JSON-formatted STRING>
  • json_expr: ARRAY<JSON>

Beispiele

Damit werden Elemente in JSON in ein Array von JSON-Werten extrahiert:

SELECT JSON_EXTRACT_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS json_array;

+---------------------------------+
| json_array                      |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

Damit werden die Elemente in einem JSON-formatierten String in ein String-Array extrahiert:

SELECT JSON_EXTRACT_ARRAY('[1,2,3]') AS string_array;

+--------------+
| string_array |
+--------------+
| [1, 2, 3]    |
+--------------+

Damit wird ein String-Array extrahiert und in ein ganzzahliges Array umgewandelt:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_EXTRACT_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

+---------------+
| integer_array |
+---------------+
| [1, 2, 3]     |
+---------------+

Damit werden Stringwerte in einem JSON-formatierten String in ein Array extrahiert:

-- Doesn't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Dadurch werden nur die Elemente im Attribut fruit in ein Array extrahiert:

SELECT JSON_EXTRACT_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}]}',
  '$.fruit'
) AS string_array;

+-------------------------------------------------------+
| string_array                                          |
+-------------------------------------------------------+
| [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
+-------------------------------------------------------+

Diese beiden Anweisungen sind gleichwertig:

SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

-- The queries above produce the following result:
+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

In Fällen, in denen ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit einfachen Anführungszeichen und Klammern wie [' '] maskieren. Beispiel:

SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;

+-----------+
| hello     |
+-----------+
| ["world"] |
+-----------+

In den folgenden Beispielen wird gezeigt, wie ungültige Anfragen und leere Arrays gehandhabt werden:

  • Wenn ein JSONPath ungültig ist, wird ein Fehler ausgegeben.
  • Wenn ein JSON-formatierter String ungültig ist, wird NULL ausgegeben.
  • Leere Arrays im JSON-formatierten String sind zulässig.
-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

JSON_QUERY_ARRAY

JSON_QUERY_ARRAY(json_string_expr[, json_path])
JSON_QUERY_ARRAY(json_expr[, json_path])

Beschreibung

Extrahiert ein Array von JSON-Werten, wie Arrays oder Objekte, und skalare Werte im JSON-Format, wie Strings, Zahlen und boolesche Werte. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Beispiel:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h., dass alle Daten analysiert werden.

Rückgabetyp

  • json_string_expr: ARRAY<JSON-formatted STRING>
  • json_expr: ARRAY<JSON>

Beispiele

Damit werden Elemente in JSON in ein Array von JSON-Werten extrahiert:

SELECT JSON_QUERY_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS json_array;

+---------------------------------+
| json_array                      |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

Damit werden die Elemente in einem JSON-formatierten String in ein String-Array extrahiert:

SELECT JSON_QUERY_ARRAY('[1,2,3]') AS string_array;

+--------------+
| string_array |
+--------------+
| [1, 2, 3]    |
+--------------+

Damit wird ein String-Array extrahiert und in ein ganzzahliges Array umgewandelt:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_QUERY_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

+---------------+
| integer_array |
+---------------+
| [1, 2, 3]     |
+---------------+

Damit werden Stringwerte in einem JSON-formatierten String in ein Array extrahiert:

-- Doesn't strip the double quotes
SELECT JSON_QUERY_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_VALUE(string_element, '$')
  FROM UNNEST(JSON_QUERY_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Dadurch werden nur die Elemente im Attribut fruit in ein Array extrahiert:

SELECT JSON_QUERY_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}]}',
  '$.fruit'
) AS string_array;

+-------------------------------------------------------+
| string_array                                          |
+-------------------------------------------------------+
| [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
+-------------------------------------------------------+

Diese beiden Anweisungen sind gleichwertig:

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

Falls ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren: " " Beispiel:

SELECT JSON_QUERY_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

+-----------+
| hello     |
+-----------+
| ["world"] |
+-----------+

In den folgenden Beispielen wird gezeigt, wie ungültige Anfragen und leere Arrays gehandhabt werden:

-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_QUERY_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

JSON_EXTRACT_STRING_ARRAY

JSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])
JSON_EXTRACT_STRING_ARRAY(json_expr[, json_path])

Beschreibung

Extrahiert ein Array von skalaren Werten und gibt ein Array mit stringcodierten Werten zurück. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit einfachen Anführungszeichen und Klammern maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Beispiel:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h., dass alle Daten analysiert werden.

Vorsichtsmaßnahmen:

  • Eine JSON-null im Eingabearray erzeugt eine SQL-NULL als Ausgabe für diese JSON-null. Wenn die Ausgabe ein NULL-Arrayelement enthält, wird ein Fehler erzeugt, weil die endgültige Ausgabe kein Array mit NULL-Werten sein kann.
  • Wenn ein JSONPath ein Array enthält, das skalare Objekte und einen JSON-null enthält, muss die Ausgabe der Funktion transformiert werden, da die endgültige Ausgabe kein Array mit NULL-Werten sein kann.

Rückgabetyp

ARRAY<STRING>

Beispiele

Damit werden Elemente in JSON in ein String-Array extrahiert:

SELECT JSON_EXTRACT_STRING_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Im folgenden Beispiel wird verglichen, wie Ergebnisse für die Funktionen JSON_EXTRACT_ARRAY und JSON_EXTRACT_STRING_ARRAY zurückgegeben werden.

SELECT JSON_EXTRACT_ARRAY('["apples","oranges"]') AS json_array,
JSON_EXTRACT_STRING_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

Damit werden die Elemente in einem JSON-formatierten String in ein String-Array extrahiert:

-- Strips the double quotes
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

Damit wird ein String-Array extrahiert und in ein ganzzahliges Array umgewandelt:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_EXTRACT_STRING_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

+---------------+
| integer_array |
+---------------+
| [1, 2, 3]     |
+---------------+

Diese beiden Anweisungen sind gleichwertig:

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

-- The queries above produce the following result:
+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

In Fällen, in denen ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit einfachen Anführungszeichen und Klammern wie [' '] maskieren. Beispiel:

SELECT JSON_EXTRACT_STRING_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;

+---------+
| hello   |
+---------+
| [world] |
+---------+

In den folgenden Beispielen wird gezeigt, wie ungültige Anfragen und leere Arrays gehandhabt werden:

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON formatted string is invalid, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY('}}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY(NULL,'$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo"}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of mixed scalar and non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_EXTRACT_STRING_ARRAY('["world", 1, null]') AS result;

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])
JSON_VALUE_ARRAY(json_expr[, json_path])

Beschreibung

Extrahiert ein Array von skalaren Werten und gibt ein Array mit stringcodierten Werten zurück. Ein skalarer Wert kann einen String, eine Zahl oder einen booleschen Wert darstellen. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren.

  • json_string_expr: Ein String im JSON-Format. Beispiel:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Beispiel:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: Der JSONpath. Er identifiziert die Daten, die Sie aus der Eingabe erhalten möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h., dass alle Daten analysiert werden.

Vorsichtsmaßnahmen:

  • Eine JSON-null im Eingabearray erzeugt eine SQL-NULL als Ausgabe für diese JSON-null. Wenn die Ausgabe ein NULL-Arrayelement enthält, wird ein Fehler erzeugt, weil die endgültige Ausgabe kein Array mit NULL-Werten sein kann.
  • Wenn ein JSONPath ein Array enthält, das skalare Objekte und einen JSON-null enthält, muss die Ausgabe der Funktion transformiert werden, da die endgültige Ausgabe kein Array mit NULL-Werten sein kann.

Rückgabetyp

ARRAY<STRING>

Beispiele

Damit werden Elemente in JSON in ein String-Array extrahiert:

SELECT JSON_VALUE_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Im folgenden Beispiel wird verglichen, wie Ergebnisse für die Funktionen JSON_QUERY_ARRAY und JSON_VALUE_ARRAY zurückgegeben werden.

SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array,
       JSON_VALUE_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

Damit werden die Elemente in einem JSON-formatierten String in ein String-Array extrahiert:

-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

Damit wird ein String-Array extrahiert und in ein ganzzahliges Array umgewandelt:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_VALUE_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

+---------------+
| integer_array |
+---------------+
| [1, 2, 3]     |
+---------------+

Diese beiden Anweisungen sind gleichwertig:

SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Falls ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren: " " Beispiel:

SELECT JSON_VALUE_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

+---------+
| hello   |
+---------+
| [world] |
+---------+

In den folgenden Beispielen wird gezeigt, wie ungültige Anfragen und leere Arrays gehandhabt werden:

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL,'$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_VALUE_ARRAY('["world", 1, null]') AS result;

PARSE_JSON

PARSE_JSON(json_string_expr[, wide_number_mode=>{ 'exact' | 'round' }])

Beschreibung

Gibt für einen STRING-SQL-Wert einen JSON-SQL-Wert zurück. Der Wert STRING stellt einen JSON-Wert im Stringformat dar.

Diese Funktion unterstützt ein optionales obligatorisch-benanntes Argument mit dem Namen wide_number_mode, das bestimmt, wie Zahlen verarbeitet werden, die nicht in einem JSON-Wert ohne Genauigkeitsverlust gespeichert werden können. Bei Verwendung muss wide_number_mode einen der folgenden Werte enthalten:

  • exact: Akzeptiert nur Zahlen, die ohne Genauigkeitsverlust gespeichert werden können. Wenn eine Zahl auftritt, die nicht ohne Genauigkeitsverlust gespeichert werden kann, gibt die Funktion einen Fehler aus.
  • round: Wenn eine Zahl, die nicht ohne Genauigkeitsverlust gespeichert werden kann, auftritt, versuchen Sie, diese auf eine Zahl zu runden, die ohne Genauigkeitsverlust gespeichert werden kann. Wenn die Zahl nicht gerundet werden kann, gibt die Funktion einen Fehler aus.

Wenn wide_number_mode nicht verwendet wird, enthält die Funktion implizit wide_number_mode=>'exact'. Wenn eine Zahl in einem JSON-Objekt oder Array angezeigt wird, wird das Argument wide_number_mode auf die Zahl im Objekt oder Array angewendet.

Zahlen aus den folgenden Domains können ohne Genauigkeitsverlust in JSON gespeichert werden:

  • 64-Bit-Ganzzahlen mit/ohne Vorzeichen, z. B. INT64
  • FLOAT64

Rückgabetyp

JSON

Beispiele

Im folgenden Beispiel wird ein JSON-formatierter String in JSON konvertiert.

SELECT PARSE_JSON('{"coordinates":[10,20],"id":1}') AS json_data;

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

Die folgenden Abfragen schlagen fehl:

  • Die übergebene Zahl kann nicht ohne Genauigkeitsverlust gespeichert werden.
  • wide_number_mode=>'exact' wird implizit in der ersten Abfrage und explizit in der zweiten Abfrage verwendet.
SELECT PARSE_JSON('{"id":922337203685477580701}') AS json_data; -- fails
SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'exact') AS json_data; -- fails

Die folgende Abfrage rundet die Zahl auf eine Zahl, die in JSON gespeichert werden kann.

SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'round') AS json_data;

+--------------------------------+
| json_data                      |
+--------------------------------+
| {"id":9.223372036854776e+20}   |
+--------------------------------+

TO_JSON

TO_JSON(sql_value[, stringify_wide_numbers=>{ TRUE | FALSE }])

Beschreibung

Gibt für einen SQL-Wert einen JSON-Wert zurück. Der Wert muss ein unterstützter BigQuery-Datentyp sein. Die BigQuery-Datentypen, die von dieser Funktion unterstützt werden, und ihre JSON-Codierungen

Diese Funktion unterstützt ein optionales obligatorisch-benanntes Argument mit dem Namen stringify_wide_numbers.

  • Wenn dieses Argument TRUE ist, werden numerische Werte außerhalb der FLOAT64-Domain als Strings codiert.
  • Wenn dieses Argument nicht verwendet wird oder FALSE ist, werden numerische Werte außerhalb der FLOAT64-Domain nicht als Strings codiert, sondern als JSON-Nummern gespeichert. Wenn ein numerischer Wert nicht ohne Genauigkeitsverlust in JSON gespeichert werden kann, wird ein Fehler ausgegeben.

Folgende numerische Datentypen werden vom stringify_wide_numbers-Argument beeinflusst:

  • INT64
  • NUMERIC
  • BIGNUMERIC

Wenn einer dieser numerischen Datentypen in einem Containerdatentyp angezeigt wird, z. B. ARRAY oder STRUCT, wird das Argument stringify_wide_numbers auf die numerischen Datentypen im Containerdatentyp angewendet.

Rückgabetyp

Ein JSON-Wert

Beispiele

Im folgenden Beispiel wandelt die Abfrage Zeilen in einer Tabelle in JSON-Werte um.

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates) UNION ALL
    (SELECT 3 AS id, [50,60] AS coordinates))
SELECT TO_JSON(t) AS json_objects
FROM CoordinatesTable AS t;

+--------------------------------+
| json_objects                   |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
| {"coordinates":[30,40],"id":2} |
| {"coordinates":[50,60],"id":3} |
+--------------------------------+

Im folgenden Beispiel gibt die Abfrage einen großen numerischen Wert als JSON-String zurück.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>TRUE) as stringify_on

+--------------------+
| stringify_on       |
+--------------------+
| "9007199254740993" |
+--------------------+

Im folgenden Beispiel geben beide Abfragen einen großen numerischen Wert als JSON-Nummer zurück.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>FALSE) as stringify_off
SELECT TO_JSON(9007199254740993) as stringify_off

+------------------+
| stringify_off    |
+------------------+
| 9007199254740993 |
+------------------+

Im folgenden Beispiel werden nur große numerische Werte in JSON-Strings konvertiert.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+---------------------------+
| json_objects              |
+---------------------------+
| {"id":"9007199254740993"} |
| {"id":2}                  |
+---------------------------+

In diesem Beispiel werden die Werte 9007199254740993 (INT64) und 2.1 (FLOAT64) in den gemeinsamen Supertyp FLOAT64 konvertiert, der nicht vom Argument stringify_wide_numbers betroffen ist.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2.1 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+------------------------------+
| json_objects                 |
+------------------------------+
| {"id":9.007199254740992e+15} |
| {"id":2.1}                   |
+------------------------------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Beschreibung

Nimmt einen SQL-Wert an und gibt eine JSON-formatierte Stringdarstellung des Werts zurück. Der Wert muss ein unterstützter BigQuery-Datentyp sein. Die BigQuery-Datentypen, die von dieser Funktion unterstützt werden, und ihre JSON-Codierungen

Diese Funktion unterstützt den optionalen booleschen Parameter pretty_print. Wenn pretty_print true ist, wird der zurückgegebene Wert zur besseren Lesbarkeit formatiert.

Rückgabetyp

Ein JSON-formatierter STRING

Beispiele

Wandeln Sie Zeilen in einer Tabelle in JSON-formatierte Strings um.

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates) UNION ALL
    (SELECT 3 AS id, [50,60] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t) AS json_data
FROM CoordinatesTable AS t;

+----+-------------+--------------------------------+
| id | coordinates | json_data                      |
+----+-------------+--------------------------------+
| 1  | [10, 20]    | {"id":1,"coordinates":[10,20]} |
| 2  | [30, 40]    | {"id":2,"coordinates":[30,40]} |
| 3  | [50, 60]    | {"id":3,"coordinates":[50,60]} |
+----+-------------+--------------------------------+

Konvertieren Sie Zeilen in einer Tabelle in JSON-formatierte Strings, die leicht zu lesen sind.

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t, true) AS json_data
FROM CoordinatesTable AS t;

+----+-------------+--------------------+
| id | coordinates | json_data          |
+----+-------------+--------------------+
| 1  | [10, 20]    | {                  |
|    |             |   "id": 1,         |
|    |             |   "coordinates": [ |
|    |             |     10,            |
|    |             |     20             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+
| 2  | [30, 40]    | {                  |
|    |             |   "id": 2,         |
|    |             |   "coordinates": [ |
|    |             |     30,            |
|    |             |     40             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+

STRING

STRING(json_expr)

Beschreibung

Nimmt einen JSON-Ausdruck, extrahiert einen JSON-String und gibt diesen Wert als einen SQL-STRING zurück. Ist der Ausdruck SQL-NULL, gibt die Funktion SQL-NULL zurück. Wenn der extrahierte JSON-Wert kein String ist, wird ein Fehler erzeugt.

  • json_expr: JSON. Beispiel:

    JSON '{"name": "sky", "color" : "blue"}'
    

Rückgabetyp

STRING

Beispiele

SELECT STRING(JSON '"purple"') AS color;

+--------+
| color  |
+--------+
| purple |
+--------+
SELECT STRING(JSON_QUERY(JSON '{"name": "sky", "color": "blue"}', "$.color")) AS color;

+-------+
| color |
+-------+
| blue  |
+-------+

Die folgenden Beispiele zeigen, wie ungültige Anfragen verarbeitet werden:

-- An error is thrown if the JSON is not of type string.
SELECT STRING(JSON '123') AS result; -- Throws an error
SELECT STRING(JSON 'null') AS result; -- Throws an error
SELECT SAFE.STRING(JSON '123') AS result; -- Returns a SQL NULL

BOOL

BOOL(json_expr)

Beschreibung

Verwendet einen JSON-Ausdruck, extrahiert einen booleschen JSON-Wert und gibt diesen Wert als SQL-BOOL zurück. Ist der Ausdruck SQL-NULL, gibt die Funktion SQL-NULL zurück. Wenn der extrahierte JSON-Wert kein boolescher Wert ist, wird ein Fehler erzeugt.

  • json_expr: JSON. Beispiel:

    JSON '{"name": "sky", "color" : "blue"}'
    

Rückgabetyp

BOOL

Beispiele

SELECT BOOL(JSON 'true') AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+
SELECT BOOL(JSON_QUERY(JSON '{"hotel class": "5-star", "vacancy": true}', "$.vacancy")) AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+

Die folgenden Beispiele zeigen, wie ungültige Anfragen verarbeitet werden:

-- An error is thrown if JSON is not of type bool.
SELECT BOOL(JSON '123') AS result; -- Throws an error
SELECT BOOL(JSON 'null') AS result; -- Throw an error
SELECT SAFE.BOOL(JSON '123') AS result; -- Returns a SQL NULL

INT64

INT64(json_expr)

Beschreibung

Verwendet einen JSON-Ausdruck, extrahiert eine JSON-Nummer und gibt diesen Wert als SQL-INT64 zurück. Ist der Ausdruck SQL-NULL, gibt die Funktion SQL-NULL zurück. Wenn die extrahierte JSON-Nummer einen Bruchteil enthält oder sich außerhalb der INT64-Domain befindet, wird ein Fehler erzeugt.

  • json_expr: JSON. Beispiel:

    JSON '{"name": "sky", "color" : "blue"}'
    

Rückgabetyp

INT64

Beispiele

SELECT INT64(JSON '2005') AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+
SELECT INT64(JSON_QUERY(JSON '{"gate": "A4", "flight_number": 2005}', "$.flight_number")) AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+
SELECT INT64(JSON '10.0') AS score;

+-------+
| score |
+-------+
| 10    |
+-------+

Die folgenden Beispiele zeigen, wie ungültige Anfragen verarbeitet werden:

-- An error is thrown if JSON is not a number or cannot be converted to a 64-bit integer.
SELECT INT64(JSON '10.1') AS result;  -- Throws an error
SELECT INT64(JSON '"strawberry"') AS result; -- Throws an error
SELECT INT64(JSON 'null') AS result; -- Throws an error
SELECT SAFE.INT64(JSON '"strawberry"') AS result;  -- Returns a SQL NULL

FLOAT64

FLOAT64(json_expr[, wide_number_mode=>{ 'exact' | 'round' }])

Beschreibung

Verwendet einen JSON-Ausdruck, extrahiert eine JSON-Nummer und gibt diesen Wert als einen SQL-FLOAT64 zurück. Ist der Ausdruck SQL-NULL, gibt die Funktion SQL-NULL zurück. Wenn der extrahierte JSON-Wert keine Zahl ist, wird ein Fehler erzeugt.

  • json_expr: JSON. Beispiel:

    JSON '{"name": "sky", "color" : "blue"}'
    

Diese Funktion unterstützt ein optionales obligatorisches Argument mit dem Namen wide_number_mode, das bestimmt, wie Zahlen verarbeitet werden, die nicht als FLOAT64-Wert ohne Genauigkeitsverlust dargestellt werden können.

Dieses Argument akzeptiert einen der beiden Werte, bei denen die Groß- und Kleinschreibung berücksichtigt wird:

  • "genau": Die Funktion schlägt fehl, wenn das Ergebnis nicht als Genauigkeitsverlust als FLOAT64 dargestellt werden kann.
  • "roundround": Der in JSON gespeicherte numerische Wert wird auf FLOAT64 gerundet. Wenn eine solche Rundung nicht möglich ist, schlägt die Funktion fehl. Dies ist der Standardwert, wenn das Argument nicht angegeben ist.

Rückgabetyp

FLOAT64

Beispiele

SELECT FLOAT64(JSON '9.8') AS velocity;

+----------+
| velocity |
+----------+
| 9.8      |
+----------+
SELECT FLOAT64(JSON_QUERY(JSON '{"vo2_max": 39.1, "age": 18}', "$.vo2_max")) AS vo2_max;

+---------+
| vo2_max |
+---------+
| 39.1    |
+---------+
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'round') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+
SELECT FLOAT64(JSON '18446744073709551615') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+

Die folgenden Beispiele zeigen, wie ungültige Anfragen verarbeitet werden:

-- An error is thrown if JSON is not of type FLOAT64.
SELECT FLOAT64(JSON '"strawberry"') AS result;
SELECT FLOAT64(JSON 'null') AS result;

-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'EXACT') as result;
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'exac') as result;

-- An error is thrown because the number cannot be converted to DOUBLE without loss of precision
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'exact') as result;

-- Returns a SQL NULL
SELECT SAFE.FLOAT64(JSON '"strawberry"') AS result;

JSON_TYPE

JSON_TYPE(json_expr)

Beschreibung

Verwendet einen JSON-Ausdruck und gibt den Typ des äußeren JSON-Werts als SQL-STRING zurück. Die Namen dieser JSON-Typen können zurückgegeben werden:

  • object
  • array
  • string
  • number
  • boolean
  • null

Ist der Ausdruck SQL NULL, gibt die Funktion SQL NULL zurück. Ist der extrahierte JSON-Wert kein gültiger JSON-Typ, wird ein Fehler erzeugt.

  • json_expr: JSON. Beispiel:

    JSON '{"name": "sky", "color" : "blue"}'
    

Rückgabetyp

STRING

Beispiele

SELECT json_val, JSON_TYPE(json_val) AS type
FROM
  UNNEST(
    [
      JSON '"apple"',
      JSON '10',
      JSON '3.14',
      JSON 'null',
      JSON '{"city": "New York", "State": "NY"}',
      JSON '["apple", "banana"]',
      JSON 'false'
    ]
  ) AS json_val;

+----------------------------------+---------+
| json_val                         | type    |
+----------------------------------+---------+
| "apple"                          | string  |
| 10                               | number  |
| 3.14                             | number  |
| null                             | null    |
| {"State":"NY","city":"New York"} | object  |
| ["apple","banana"]               | array   |
| false                            | boolean |
+----------------------------------+---------+

JSON-Codierungen

Die folgende Tabelle enthält gängige Codierungen, die verwendet werden, wenn ein SQL-Wert als JSON-Wert mit der Funktion TO_JSON_STRING oder TO_JSON codiert wird.

Von SQL In JSON Beispiele
NULL

null

SQL-Eingabe: NULL
JSON-Ausgabe: null
BOOL Boolesch SQL-Eingabe: TRUE
JSON-Ausgabe: true

SQL-Eingabe: FALSE
JSON-Ausgabe: false
INT64

(Nur TO_JSON_STRING)

Zahl oder String

Codiert als Zahl, wenn der Wert im Bereich [-253, 253] liegt. Dies ist der Bereich der Ganzzahlen, die verlustfrei als IEEE 754 mit doppelter Genauigkeit mit Gleitkommazahlen dargestellt wird. Ein Wert außerhalb dieses Bereichs wird als String codiert.

SQL-Eingabe: 9007199254740992
JSON-Ausgabe: 9007199254740992

SQL-Eingabe: 9007199254740993
JSON-Ausgabe: "9007199254740993"
INT64

(Nur TO_JSON)

Zahl oder String

Wenn das Argument stringify_wide_numbers TRUE lautet und der Wert außerhalb der FLOAT64-Domain liegt, wird der Wert als String codiert. Wenn der Wert nicht ohne Genauigkeitsverlust in JSON gespeichert werden kann, schlägt die Funktion fehl. Andernfalls wird der Wert als Zahl codiert.

Wenn stringify_wide_numbers nicht verwendet wird oder FALSE ist, werden numerische Werte außerhalb der FLOAT64-Domain nicht als Strings codiert, sondern als JSON-Nummern gespeichert. Wenn ein numerischer Wert nicht ohne Genauigkeitsverlust in JSON gespeichert werden kann, wird ein Fehler ausgegeben.

SQL-Eingabe: 9007199254740992
JSON-Ausgabe: 9007199254740992

SQL-Eingabe: 9007199254740993
JSON Ausgabe: 9007199254740993

SQL-Eingabe mit stringify_wide_numbers=>TRUE: 9007199254740992
JSON-Ausgabe: 9007199254740992

SQL-Eingabe mit stringify_wide_numbers=>TRUE: 9007199254740993
JSON-Ausgabe: "9007199254740993"
NUMERIC
BIGNUMERIC

(Nur TO_JSON_STRING)

Zahl oder String

Codiert als Zahl, wenn der Wert im Bereich [-253, 253] liegt und keine Bruchzahl ist. Ein Wert außerhalb dieses Bereichs wird als String codiert.

SQL-Eingabe: -1
JSON-Ausgabe: -1

SQL-Eingabe: 0
JSON-Ausgabe: 0

SQL-Eingabe: 9007199254740993
JSON-Ausgabe: "9007199254740993"

SQL-Eingabe: 123.56
JSON-Ausgabe: "123.56"
NUMERIC
BIGNUMERIC

(Nur TO_JSON)

Zahl oder String

Wenn das Argument stringify_wide_numbers TRUE lautet und der Wert außerhalb der FLOAT64-Domain liegt, wird er als String codiert. Andernfalls wird er als Zahl codiert.

SQL-Eingabe: -1
JSON-Ausgabe: -1

SQL-Eingabe: 0
JSON Ausgabe: 0

SQL-Eingabe: 9007199254740993
JSON-Ausgabe: 9007199254740993

SQL-Eingabe: 123.56
JSON-Ausgabe: 123.56

SQL-Eingabe mit stringify_wide_numbers=>TRUE: 9007199254740993
JSON-Ausgabe: "9007199254740993"

SQL-Eingabe mit stringify_wide_numbers=>TRUE: 123.56
JSON Ausgabe: 123.56
FLOAT64

Zahl oder String

+/-inf und NaN sind als Infinity, -Infinity und NaN codiert. Andernfalls wird dieser Wert als Zahl codiert.

SQL-Eingabe: 1.0
JSON-Ausgabe: 1

SQL-Eingabe: 9007199254740993
JSON-Ausgabe: 9007199254740993

SQL-Eingabe: "+inf"
JSON-Ausgabe: "Infinity"

SQL-Eingabe: "-inf"
JSON-Ausgabe: "-Infinity"

SQL-Eingabe: "NaN"
JSON-Ausgabe: "NaN"
STRING

String

Codiert als String, codiert gemäß dem JSON-Standard. Insbesondere ", \ und die Steuerelemente von U+0000 bis U+001F werden maskiert.

SQL-Eingabe: "abc"
JSON-Ausgabe: "abc"

SQL-Eingabe: "\"abc\""
JSON-Ausgabe: "\"abc\""
BYTES

String

Verwendet die Base64-Datencodierung gemäß RFC 4648.

SQL-Eingabe: b"Google"
JSON-Ausgabe: "R29vZ2xl"
DATE String SQL-Eingabe: DATE '2017-03-06'
JSON-Ausgabe: "2017-03-06"
TIMESTAMP

String

Codiert als ISO 8601-Datum und -Uhrzeit, wobei T das Datum von der Uhrzeit trennt und Z (Zulu/UTC) die Zeitzone darstellt.

SQL-Eingabe: TIMESTAMP '2017-03-06 12:34:56.789012'
JSON-Ausgabe: "2017-03-06T12:34:56.789012Z"
DATETIME

String

Codiert als ISO 8601 mit Datum und Uhrzeit, wobei T das Datum und die Uhrzeit trennt.

SQL-Eingabe: DATETIME '2017-03-06 12:34:56.789012'
JSON-Ausgabe: "2017-03-06T12:34:56.789012"
TIME

String

Codiert als ISO 8601-Zeit.

SQL-Eingabe: TIME '12:34:56.789012'
JSON-Ausgabe: "12:34:56.789012"
JSON

Daten der JSON-Eingabe

SQL-Eingabe: JSON '{"item": "pen", "price": 10}'
JSON-Ausgabe: {"item":"pen", "price":10}

SQL-Eingabe: [1, 2, 3]
JSON-Ausgabe: [1, 2, 3]
ARRAY

array

Kann null oder mehr Elemente enthalten.

SQL-Eingabe: ["red", "blue", "green"]
JSON-Ausgabe: ["red","blue","green"]

SQL-Eingabe: [1, 2, 3]
JSON-Ausgabe: [1,2,3]
STRUCT

Objekt

Das Objekt kann null oder mehr Schlüssel/Wert-Paare enthalten. Jeder Wert wird entsprechend seinem Typ formatiert.

Bei TO_JSON ist ein Feld im Ausgabestring enthalten. Duplikate dieses Felds werden weggelassen. Für TO_JSON_STRING sind ein Feld und alle Duplikate dieses Felds im Ausgabestring enthalten.

Anonyme Felder werden mit "" dargestellt.

Ungültige UTF-8-Feldnamen können dazu führen, dass das JSON-Format nicht geparst werden kann. Stringwerte werden nach dem JSON-Standard maskiert. ", \ und die Steuerzeichen von U+0000 bis U+001F werden maskiert.

SQL-Eingabe: STRUCT(12 AS purchases, TRUE AS inStock)
JSON-Ausgabe: {"inStock": true,"purchases":12}

JSONPath-Format

Mit dem JSONPath-Format können Sie die Werte identifizieren, die Sie aus einem JSON-formatierten String erhalten möchten. Das JSONPath-Format unterstützt diese Operatoren:

Operator Beschreibung Beispiele
$ Root-Objekt oder Element Das JSONPath-Format muss mit diesem Operator beginnen, der sich auf die äußerste Ebene des JSON-formatierten Strings bezieht.

JSON-formatierter String:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON-Pfad:
"$"

JSON-Ergebnis:
{"class":{"students":[{"name":"Jane"}]}}

. Untergeordneter Operator Sie können untergeordnete Werte mithilfe der Punktnotation identifizieren.

JSON-formatierter String:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON-Pfad:
"$.class.students"

JSON-Ergebnis:
[{"name":"Jane"}]

[] Tiefgestellt-Operator Wenn das JSON-Objekt ein Array ist, können Sie Klammern verwenden, um den Arrayindex anzugeben.

JSON-formatierter String:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

JSON-Pfad:
"$.class.students[0]"

JSON-Ergebnis:
{"name":"Jane"}

Wenn ein Schlüssel in einer JSON-Funktion einen Operator im JSON-Format enthält, beachten Sie die Informationen zu den einzelnen JSON-Funktionen zur Maskierung dieser Zeichen.

Eine JSON-Funktion gibt NULL zurück, wenn das JSONPath-Format mit keinem Wert in einem JSON-formatierten String übereinstimmt. Wenn der ausgewählte Wert für eine skalare Funktion nicht skalar ist, z. B. ein Objekt oder ein Array, gibt die Funktion NULL zurück. Wenn das JSONPath-Format ungültig ist, wird ein Fehler erzeugt.