JSON-Funktionen

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. Wenn json_path eine null im JSON-Format zurückgibt, wird dies in eine NULL 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. Wenn json_path eine null im JSON-Format zurückgibt, wird dies in eine NULL 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.