JSON-Funktionen

Cloud Spanner SQL unterstützt die folgenden Funktionen, die JSON-Daten abrufen und 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, bzw. einen JSON-Skalarwert wie String, Ganzzahl oder 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

JSON_QUERY(json_string_expr, json_path)

Beschreibung

Extrahiert einen JSON-Wert, z. B. ein Array oder Objekt, bzw. einen JSON-Skalarwert wie String, Ganzzahl oder 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"}]}}'
```
Extrahiert einen SQL-NULL, wenn ein JSON-formatierter String "null" gefunden wird. Beispiel:
```
SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
```

json_path: Der JSONpath. Gibt den Wert oder die Werte an, die Sie aus dem JSON-formatierten String abrufen möchten.

SELECT JSON_QUERY('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL

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

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

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 dieser optionale Parameter nicht angegeben wird, wird das JSONPath-Symbol $ angewendet, d. h. der gesamte JSON-formatierte String wird analysiert.

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

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

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.