JSON-Funktionen in Standard-SQL

BigQuery unterstützt Funktionen, die Ihnen beim Abrufen von Daten helfen, die in JSON-formatierten Strings gespeichert sind, und Funktionen, mit denen Sie Daten in JSON-formatierte Strings umwandeln 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 Ganzzahl oder ein boolescher Wert. JSON-formatierte STRING
JSON_VALUE Extrahiert einen skalaren Wert. Ein skalarer Wert kann einen String, eine Ganzzahl 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, Ganzzahlen und boolesche Werte. ARRAY<JSON-formatted STRING>
JSON_VALUE_ARRAY Extrahiert ein Array von skalaren Werten. Ein skalarer Wert kann einen String, eine Ganzzahl 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 Ganzzahl oder ein boolescher Wert. JSON-formatierte STRING
JSON_EXTRACT_SCALAR Extrahiert einen skalaren Wert. Ein skalarer Wert kann einen String, eine Ganzzahl 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, Ganzzahlen und boolesche Werte. ARRAY<JSON-formatted STRING>
JSON_EXTRACT_STRING_ARRAY Extrahiert ein Array von skalaren Werten. Ein skalarer Wert kann einen String, eine Ganzzahl 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
TO_JSON_STRING Gibt einen JSON-formatierten String zurück, der „value“ darstellt. JSON-formatierte STRING

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)

Beschreibung

Extrahiert einen JSON-Wert, z. B. ein Array oder Objekt oder einen skalaren JSON-formatierten Wert, wie einen String, eine Ganzzahl oder ein boolescher 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"}]}}

  • json_path: Der JSONpath. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String abrufen möchten. Wenn json_path eine null im JSON-Format zurückgibt, wird dies in eine NULL im SQL-Format konvertiert.

Wenn Sie nicht skalare Werte wie Arrays in die Extraktion aufnehmen möchten, verwenden Sie JSON_EXTRACT. Wenn Sie nur skalare Werte wie Strings, Ganzzahlen und boolesche Werte extrahieren möchten, verwenden Sie JSON_EXTRACT_SCALAR.

Rückgabetyp

Ein JSON-formatierter STRING

Beispiele

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"}] |
+------------------------------------+

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)

Beschreibung

Extrahiert einen JSON-Wert, z. B. ein Array oder Objekt oder einen skalaren JSON-formatierten Wert, wie einen String, eine Ganzzahl oder ein boolescher 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"}]}}

  • json_path: Der JSONpath. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String abrufen möchten. Wenn json_path eine null im JSON-Format zurückgibt, wird dies in eine NULL im SQL-Format konvertiert.

Wenn Sie nicht skalare Werte wie Arrays in die Extraktion aufnehmen möchten, verwenden Sie JSON_QUERY. Wenn Sie nur skalare Werte wie Strings, Ganzzahlen und boolesche Werte extrahieren möchten, verwenden Sie JSON_VALUE.

Rückgabetyp

Ein JSON-formatierter STRING

Beispiele

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"}] |
+------------------------------------+

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr, json_path)

Beschreibung

Extrahiert einen skalaren Wert und gibt ihn dann als String zurück. Ein skalarer Wert kann einen String, eine Ganzzahl 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_path: Der JSONpath. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String abrufen möchten. Wenn json_path einen JSON-null oder einen nicht-skalaren Wert zurückgibt (mit anderen Worten, wenn json_path auf ein Objekt oder ein Array verweist), wird ein NULL-SQL zurückgegeben.

Wenn Sie nur skalare Werte wie Strings, Ganzzahlen und boolesche Werte extrahieren möchten, verwenden Sie JSON_EXTRACT_SCALAR. Wenn Sie nicht skalare Werte wie Arrays in die Extraktion aufnehmen möchten, verwenden Sie JSON_EXTRACT.

Rückgabetyp

STRING

Beispiele

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)

Beschreibung

Extrahiert einen skalaren Wert und gibt ihn dann als String zurück. Ein skalarer Wert kann einen String, eine Ganzzahl 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_path: Der JSONpath. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String abrufen möchten. Wenn json_path einen JSON-null oder einen nicht-skalaren Wert zurückgibt (mit anderen Worten, wenn json_path auf ein Objekt oder ein Array verweist), wird ein NULL-SQL zurückgegeben.

Wenn Sie nur skalare Werte wie Strings, Ganzzahlen und boolesche Werte extrahieren möchten, verwenden Sie JSON_VALUE. Wenn Sie nicht skalare Werte wie Arrays in die Extraktion aufnehmen möchten, verwenden Sie JSON_QUERY.

Rückgabetyp

STRING

Beispiele

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])

Beschreibung

Extrahiert ein Array von JSON-Werten, wie Arrays oder Objekte, und skalare Werte im JSON-Format, wie Strings, Ganzzahlen 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_path: Der JSONpath. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String abrufen möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h. der gesamte JSON-formatierte String wird analysiert.

Rückgabetyp

ARRAY<JSON-FORMATTED STRING>

Beispiele

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])

Beschreibung

Extrahiert ein Array von JSON-Werten, wie Arrays oder Objekte, und skalare Werte im JSON-Format, wie Strings, Ganzzahlen 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_path: Der JSONpath. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String abrufen möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h. der gesamte JSON-formatierte String wird analysiert.

Rückgabetyp

ARRAY<JSON-FORMATTED STRING>

Beispiele

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_EXTRACT_SCALAR(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])

Beschreibung

Extrahiert ein Array von skalaren Werten und gibt ein Array mit stringcodierten Werten zurück. Ein skalarer Wert kann einen String, eine Ganzzahl 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_path: Der JSONpath. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String abrufen möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h. der gesamte JSON-formatierte String wird analysiert.

Rückgabetyp

ARRAY<STRING>

Beispiele

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 |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar values and a JSON null,
-- then the output of the JSON_EXTRACT_STRING_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_EXTRACT_STRING_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_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 Ganzzahl 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_path: Der JSONpath. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String abrufen möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h. der gesamte JSON-formatierte String wird analysiert.

Rückgabetyp

ARRAY<STRING>

Beispiele

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 |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar objects and a JSON null,
-- then the output of the JSON_VALUE_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_VALUE_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Beschreibung

Gibt eine JSON-formatierte Stringdarstellung von value zurück. Diese Funktion unterstützt den optionalen booleschen Parameter pretty_print. Wenn pretty_print true ist, wird der zurückgegebene Wert zur besseren Lesbarkeit formatiert.

Datentyp der Eingabe Rückgabewert
NULL eines beliebigen Typs null
BOOL true oder false.
INT64

Entspricht CAST(value AS STRING), wenn value im Bereich [-253, 253] liegt. Dies ist der Bereich der Ganzzahlen, die als Gleitkommazahlen nach IEEE 754 mit doppelter Genauigkeit verlustfrei dargestellt werden können. Werte außerhalb dieses Bereichs werden als Strings in Anführungszeichen dargestellt. Beispiel:

-1
0
12345678901
9007199254740992
-9007199254740992
"9007199254740993"

9007199254740993 ist größer als 253 und wird daher als String in Anführungszeichen dargestellt.
NUMERIC, BIGNUMERIC

Entspricht CAST(value AS STRING), wenn value im Bereich [-253, 253] liegt und keine Bruchzahl ist. Werte außerhalb dieses Bereichs werden als Strings in Anführungszeichen dargestellt. Beispiel:

-1
0
"9007199254740993"
"123.56"
FLOAT64 +/-inf und NaN werden als Infinity, -Infinity bzw. NaN dargestellt.

Ansonsten mit CAST(value AS STRING) identisch.
STRING Stringwert in Anführungszeichen, gemäß JSON-Standard maskiert. ", \ und die Steuerzeichen von U+0000 bis U+001F werden maskiert.
BYTES

Base64-maskierter RFC 4648-Wert in Anführungszeichen. Beispiel:

"R29vZ2xl" ist die base64-Darstellung der Byte b"Google".
DATE

Datum in Anführungszeichen. Beispiel:

"2017-03-06"
TIMESTAMP

Datum/Uhrzeit gemäß ISO 8601 in Anführungszeichen, wobei T das Datum von der Uhrzeit trennt und Zulu/UTC die Zeitzone darstellt. Beispiel:

"2017-03-06T12:34:56.789012Z"
DATETIME

Datum/Uhrzeit gemäß ISO 8601 in Anführungszeichen, wobei T das Datum von der Uhrzeit trennt. Beispiel:

"2017-03-06T12:34:56.789012"
TIME

Uhrzeit gemäß ISO 8601 in Anführungszeichen. Beispiel:

"12:34:56.789012"
ARRAY

Array von null oder mehr Elementen. Jedes Element wird entsprechend seinem Typ formatiert.

Beispiel ohne Formatierung:


["red", "blue", "green"]

Beispiel mit Formatierung: 


[
  "red",
  "blue",
  "green"
]
STRUCT

Objekt, das null oder mehr Schlüssel/Wert-Paare enthält. Jeder Wert wird entsprechend seinem Typ formatiert.

Beispiel ohne Formatierung:


{"colors":["red","blue"],"purchases":12,"inStock": true}

Beispiel mit Formatierung: 


{
  "color":[
    "red",
    "blue"
   ]
  "purchases":12,
  "inStock": true
}

Felder mit identischen Namen können dazu führen, dass das JSON-Format nicht geparst werden kann. Anonyme Felder werden mit "" dargestellt. Wenn ein Feld ein nicht leeres Array oder Objekt ist, werden Elemente/Felder auf die entsprechende Ebene eingerückt.

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.

Rückgabetyp

Ein JSON-formatierter STRING

Beispiele

Umwandeln von Zeilen einer Tabelle in das JSON-Format.

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]} |
+----+-------------+--------------------------------+

Umwandeln von Zeilen einer Tabelle in JSON mit Formatierung.

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             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+

JSONPath

Die meisten JSON-Funktionen übergeben einen Parameter json_string_expr oder json_path. Der Parameter json_string_expr übergibt einen JSON-formatierten String und der Parameter json_path gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String erhalten möchten.

Der Parameter json_string_expr muss ein JSON-String im folgenden Format sein:

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

Sie erstellen den Parameter json_path im JSONPath-Format. Gemäß diesem Format muss der Parameter mit einem $-Zeichen beginnen, das sich auf die äußerste Ebene des JSON-formatierten Strings bezieht. Sie können untergeordnete Werte mithilfe von Punkten angeben. Wenn das JSON-Objekt ein Array ist, können Sie Klammern verwenden, um den Arrayindex anzugeben. Wenn die Schlüssel $, Punkte oder Klammern enthalten, beachten Sie die Informationen zu den einzelnen JSON-Funktionen zur Maskierung dieser Zeichen.

JSONPath Beschreibung Beispiel Ergebnis mit dem obigen json_string_expr
$ Root-Objekt oder Element "$" {"class":{"students":[{"name":"Jane"}]}}
. Untergeordneter Operator "$.class.students" [{"name":"Jane"}]
[] Tiefgestellt-Operator "$.class.students[0]" {"name":"Jane"}

Eine JSON-Funktion gibt NULL zurück, wenn der Parameter json_path mit keinem Wert in json_string_expr ü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 der JSONPath-Wert ungültig ist, löst die Funktion einen Fehler aus.