Cloud Spanner SQL unterstützt Funktionen, die Ihnen beim Abrufen von Daten helfen, die in JSON-formatierten Strings gespeichert sind, und beim Abrufen von Funktionen, mit denen Sie Daten in JSON-formatierte Strings umwandeln können.
Funktionsübersicht
JSON-Funktion | Beschreibung |
---|---|
JSON_QUERY |
Extrahiert einen JSON-Wert, z. B. ein Array oder Objekt, oder einen skalaren JSON-formatierten Wert, z. B. einen String, eine Ganzzahl 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_VALUE |
Extrahiert einen skalaren Wert wie einen String, eine Ganzzahl oder einen booleschen Wert. Entfernt die äußersten Anführungszeichen und nimmt die Maskierung der Werte wieder vor. Wenn ein JSON-Schlüssel ungültige JSONPath-Zeichen verwendet, können Sie diese Zeichen mit doppelten Anführungszeichen maskieren. |
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, z. B. einen String, eine Ganzzahl 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"}]}}
json_path
: Der JSONPath. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String abrufen möchten. Wennjson_path
einenull
im JSON-Format zurückgibt, wird dies in eineNULL
im SQL-Format konvertiert.
Wenn Sie Nicht-Skalarwerte wie Arrays in die Extraktion einbeziehen 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_VALUE
JSON_VALUE(json_string_expr, json_path)
Beschreibung
Extrahiert einen skalaren Wert wie einen String, eine Ganzzahl oder einen booleschen Wert. Entfernt die äußersten 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. Wennjson_path
einenull
im JSON-Format zurückgibt, wird dies in eineNULL
im SQL-Format konvertiert.
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;
+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob" | Jakob | "6" | 6 |
+-----------+-------------+----------+--------+
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 |
+-------+
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.