Cloud Spanner SQL は JSON 形式の文字列に格納されたデータを取得する関数と、データを JSON 形式の文字列に変換する関数をサポートしています。
JSON_QUERY または JSON_VALUE
JSON_QUERY(json_string_expr, json_path_format)
は、JSON 値を STRING として返します。
JSON_VALUE(json_string_expr, json_path_format)
は、スカラー JSON 値を STRING として返します。
説明
JSON 値または JSON スカラー値を文字列として抽出します。
json_string_expr
: JSON 形式の文字列。例:{"class" : {"students" : [{"name" : "Jane"}]}}
json_path_format
: JSONpath 形式。これは、JSON 形式の文字列から取得する値を指定します。json_path_format
が JSONnull
を返す場合、これは SQLNULL
に変換されます。
JSON キーが無効な JSONPath 文字を使用した場合は、二重引用符を使用して、それらの文字をエスケープできます。
例
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"}] |
+------------------------------------+
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 |
+-----------+-------------+----------+--------+
JSON キーが無効な JSONPath 文字を使用した場合は、二重引用符を使用して、それらの文字をエスケープできます。例:
SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') as hello;
+-------+
| hello |
+-------+
| world |
+-------+
JSONPath 形式
ほとんどの JSON 関数は json_string_expr
パラメータと json_path_format
パラメータで渡されます。json_string_expr
パラメータは JSON 形式の文字列で渡され、json_path_format
パラメータは JSON 形式の文字列から取得する値を指定します。
json_string_expr
パラメータは次のような形式の JSON 文字列にする必要があります。
{"class" : {"students" : [{"name" : "Jane"}]}}
JSONPath 形式を使用して json_path_format
パラメータを作成します。このパラメータの書式は $
記号で開始する必要があります。これは JSON 形式の文字列の最も外側のレベルを指すシンボルです。子の値は、ドットを使用して識別できます。JSON オブジェクトが配列の場合は、角括弧を使用して配列のインデックスを指定できます。キーに $
、ドット、角括弧が含まれている場合、エスケープする方法については、各 JSON 関数をご覧ください。
JSONPath | 説明 | 例 | 上記の json_string_expr を使用した結果 |
---|---|---|---|
$ | ルートのオブジェクトまたは要素 | "$" | {"class":{"students":[{"name":"Jane"}]}} |
. | 子の演算子 | "$.class.students" | [{"name":"Jane"}] |
[] | 添字演算子 | "$.class.students[0]" | {"name":"Jane"} |
JSON 関数は、json_path_format
パラメータが json_string_expr
内の値と一致しない場合は NULL
を返します。この関数は、スカラー関数に選択した値がオブジェクトや配列などのスカラー値ではない場合は、NULL
を返します。
JSONPath が無効な場合、この関数はエラーとなります。