JSON 함수

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이 JSON null을 반환하면 SQL NULL로 변환됩니다.

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_exprjson_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_path_format 매개 변수가 json_string_expr 값과 일치하지 않으면 JSON 함수에서 NULL을 반환합니다. 스칼라 함수에 선택된 값이 스칼라가 아닌 경우(예: 객체 또는 배열) 이 함수는 NULL를 반환합니다.

JSONPath가 잘못된 경우 함수에서 오류가 발생합니다.