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.

JSON_EXTRACT oder JSON_EXTRACT_SCALAR

JSON_EXTRACT(json_string_expr, json_path_string_literal) gibt JSON-Werte als STRING-Werte zurück.

JSON_EXTRACT_SCALAR(json_string_expr, json_path_string_literal) gibt skalare JSON-Werte als STRING-Werte zurück.

Beschreibung

Extrahiert JSON-Werte oder JSON-Skalarwerte als Strings.

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_string_literal: Das JSONpath-Format. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String erhalten möchten. Wenn json_path_string_literal JSON null zurückgibt, wird dies in SQL NULL konvertiert.

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 maskieren.

Rückgabetyp

STRINGs

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

+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob"   | Jakob       | "6"      | 6      |
+-----------+-------------+----------+--------+

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_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path_string_literal])

Beschreibung

Extrahiert ein Array aus einem JSON-formatierten String.

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_string_literal: Das JSONpath-Format. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String erhalten möchten. Wenn dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h. der gesamte JSON-formatierte String wird analysiert.

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 maskieren.

Rückgabetyp

ARRAY<STRING>

Beispiele

Dadurch 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']  |
+----------------+

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

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

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

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

-- Strip 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 in 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:

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

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

-- The queries above produce this 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 diesen Beispielen wird erläutert, wie ungültige Anfragen und leere Arrays verarbeitet 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.
JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') as result

-- If a key is not specified when a key is expected, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$') as result

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

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

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

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

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

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 Parameter pretty_print. Wenn pretty_print angegeben wurde, 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

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

[elem1,elem2,...], wobei jedes elem gemäß dem Elementtyp formatiert ist.

Beispiel mit Formatierung:

[
  elem1,
  elem2,
  ...
]

Dabei ist jedes Element gemäß dem Elementtyp formatiert. Ein leeres Array wird als [] dargestellt.

STRUCT {"field_name1":field_value1,"field_name2":field_value2,...}

Dabei ist jeder field_value gemäß seinem Typ formatiert.

Beispiel mit Formatierung:

{
  "field_name1": field_value1,
  "field_name2": field_value2,
  ...
}

Dabei ist jeder field_value gemäß seinem Typ formatiert. Wenn ein field_value ein nicht leeres ARRAY oder STRUCT ist, werden Elemente auf die entsprechende Ebene eingerückt. Ein leeres STRUCT wird als {} dargestellt.

Felder mit identischen Namen können dazu führen, dass das JSON-Format nicht geparst werden kann. 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.

Rückgabetyp

JSON-String-Darstellung des Wertes.

Beispiele

Umwandeln von Zeilen einer Tabelle in das JSON-Format.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  t,
  TO_JSON_STRING(t) AS json_row
FROM Input AS t;

Die obige Abfrage ergibt das folgende Ergebnis:

+-----------------------------------+-------------------------------------------------------+
| t                                 | json_row                                              |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}}     | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}}    |
| {[3], bar, {NULL, 2016-12-05}}    | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}}   |
+-----------------------------------+-------------------------------------------------------+

Umwandeln von Zeilen einer Tabelle in JSON mit Formatierung.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;

Die obige Abfrage ergibt das folgende Ergebnis:

+-----------------------+
| json_row              |
+-----------------------+
| {                     |
|  "x": [               |
|    1,                 |
|    2                  |
|  ],                   |
|  "y": "foo",          |
|  "s": {               |
|    "a": true,         |
|    "b": "2017-04-05"  |
|  }                    |
|}                      |
| {                     |
|  "x": null,           |
|  "y": "",             |
|  "s": {               |
|    "a": false,        |
|    "b": "0001-01-01"  |
|  }                    |
|}                      |
| {                     |
|  "x": [               |
|    3                  |
|  ],                   |
|  "y": "bar",          |
|  "s": {               |
|    "a": null,         |
|    "b": "2016-12-05"  |
|  }                    |
|}                      |
+-----------------------+

JSONPath-Format

Die meisten JSON-Funktionen übergeben einen Parameter json_string_expr oder json_path_string_literal. Der Parameter json_string_expr übergibt einen JSON-formatierten String und der Parameter json_path_string_literal 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_string_literal 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_string_literal 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.