GoogleSQL의 JSON 데이터 작업

이 문서에서는 JSON 열이 있는 테이블을 만들고, JSON 데이터를 BigQuery 테이블에 삽입하고, JSON 데이터를 쿼리하는 방법을 설명합니다.

BigQuery는 기본적으로 JSON 데이터 유형을 사용하여 JSON 데이터를 지원합니다.

JSON은 스키마를 필요로 하지 않으므로 반구조화된 데이터를 허용하는 널리 사용되는 형식입니다. 애플리케이션은 읽기 스키마(schema-on-read) 접근 방식을 사용할 수 있습니다. 이 접근 방법에서는 애플리케이션이 데이터를 수집한 다음 해당 데이터의 스키마에 대한 가정을 기반으로 쿼리합니다. 이 접근 방식은 STRUCT 유형의 열에 저장된 모든 값에 적용되는 고정 스키마가 필요한 BigQuery의 STRUCT 유형과 다릅니다.

JSON 데이터 유형을 사용하면 JSON 데이터의 스키마를 미리 제공하지 않고도 반구조화된 JSON을 BigQuery로 로드할 수 있습니다. 이렇게 하면 항상 고정 스키마와 데이터 유형을 준수하지 않는 데이터를 저장하고 쿼리할 수 있습니다. BigQuery는 JSON 데이터를 JSON 데이터 유형으로 수집하여 각 JSON 필드를 개별적으로 인코딩하고 처리할 수 있습니다. 그런 다음 필드 액세스 연산자를 사용하여 JSON 데이터 내에서 필드 및 배열 요소의 값을 쿼리할 수 있습니다. 이렇게 하면 JSON 쿼리를 직관적이고 비용 효율적으로 만듭니다.

제한사항

  • 일괄 로드 작업을 사용하여 JSON 데이터를 테이블에 수집하는 경우 소스 데이터는 CSV, Avro 또는 JSON 형식이어야 합니다. 다른 일괄 로드 형식은 지원되지 않습니다.
  • JSON 데이터 유형의 중첩 한도는 500입니다.
  • legacy SQL을 사용하여 JSON 유형이 포함된 테이블을 쿼리할 수 없습니다.
  • 행 수준 액세스 정책은 JSON 열에 적용할 수 없습니다.

JSON 데이터 유형의 속성에 대한 자세한 내용은 JSON 유형을 참조하세요.

먼저 JSON 열을 사용하여 테이블을 만듭니다.

SQL 또는bq 명령줄 도구를 사용하여 JSON 열이 있는 빈 테이블을 만들 수 있습니다.

SQL

CREATE TABLE 문을 사용하고 JSON 유형으로 열을 선언합니다.

  1. Google Cloud 콘솔에서 BigQuery 페이지로 이동합니다.

    BigQuery로 이동

  2. 쿼리 편집기에서 다음 문을 입력합니다.

    CREATE TABLE mydataset.table1(
  id INT64,
  cart JSON
);

  3. 실행을 클릭합니다.

쿼리를 실행하는 방법에 대한 자세한 내용은 대화형 쿼리 실행을 참조하세요.

bq

bq mk 명령어를 사용하고 JSON 데이터 유형의 테이블 스키마를 제공합니다.

bq mk --table mydataset.table1 id:INT64,cart:JSON

등호 연산자와 비교 연산자는 JSON 유형에 정의되어 있지 않으므로 JSON 열의 테이블을 파티션으로 나누거나 클러스터링할 수 없습니다.

JSON 값 만들기

다음과 같은 방법으로 JSON 값을 만들 수 있습니다.

  • SQL을 사용하여 JSON 리터럴을 만듭니다.
  • PARSE_JSON 함수를 사용하여 STRING 값을 JSON 값으로 변환합니다.
  • TO_JSON 함수를 사용하여 SQL 값을 JSON 값으로 변환합니다.
  • JSON_ARRAY 함수를 사용하여 SQL 값에서 JSON 배열을 만듭니다.
  • JSON_OBJECT 함수를 사용하여 키-값 쌍에서 JSON 객체를 만듭니다.

JSON 값 만들기

다음 예시에서는 JSON 값을 테이블에 삽입합니다.

INSERT INTO mydataset.table1 VALUES
(1, JSON '{"name": "Alice", "age": 30}'),
(2, JSON_ARRAY(10, ['foo', 'bar'], [20, 30])),
(3, JSON_OBJECT('foo', 10, 'bar', ['a', 'b']));

STRING 유형을 JSON 유형으로 변환합니다.

다음 예시에서는 PARSE_JSON 함수를 사용하여 JSON 형식의 STRING 값을 변환합니다. 이 예시에서는 기존 테이블의 열을 JSON 유형으로 변환하고 결과를 새 테이블에 저장합니다.

CREATE OR REPLACE TABLE mydataset.table_new
AS (
  SELECT
    id, SAFE.PARSE_JSON(cart) AS cart_json
  FROM
    mydataset.old_table
);

이 예시에서 사용된 SAFE 프리픽스는 변환 오류가 NULL 값으로 반환되도록 합니다.

스키마가 적용된 데이터를 JSON으로 변환

다음 예시에서는 JSON_OBJECT 함수를 사용하여 키-값 쌍을 JSON으로 변환합니다.

WITH Fruits AS (
SELECT 0 AS id, 'color' AS k, 'Red' AS v UNION ALL
SELECT 0, 'fruit', 'apple' UNION ALL
SELECT 1, 'fruit','banana' UNION ALL
SELECT 1, 'ripe', 'true'
)

SELECT JSON_OBJECT(ARRAY_AGG(k), ARRAY_AGG(v)) AS json_data
FROM Fruits
GROUP BY id

결과는 다음과 같습니다.

+----------------------------------+
| json_data                        |
+----------------------------------+
| {"color":"Red","fruit":"apple"}  |
| {"fruit":"banana","ripe":"true"} |
+----------------------------------+

SQL 유형을 JSON 유형으로 변환

다음 예시는 TO_JSON 함수를 사용하여 SQL STRUCT 값을 JSON 값으로 변환합니다.

SELECT TO_JSON(STRUCT(1 AS id, [10,20] AS coordinates)) AS pt;

결과는 다음과 같습니다.

+--------------------------------+
| pt                             |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
+--------------------------------+

JSON 데이터 수집

다음 방법으로 JSON 데이터를 BigQuery 테이블로 수집할 수 있습니다.

CSV 파일에서 로드

다음 예시에서는 다음 레코드를 포함하는 file1.csv라는 CSV 파일이 있다고 가정합니다.

1,20
2,"""This is a string"""
3,"{""id"": 10, ""name"": ""Alice""}"

두 번째 열에는 문자열로 인코딩된 JSON 데이터가 포함됩니다. 이를 위해서는 따옴표를 CSV 형식에 맞춰 올바르게 이스케이프 처리해야 합니다. CSV 형식에서 따옴표는 두 문자 시퀀스 ""를 사용하여 이스케이프 처리됩니다.

bq 명령줄 도구를 사용하여 이 파일을 로드하려면 bq load 명령어를 실행합니다.

bq load --source_format=CSV mydataset.table1 file1.csv id:INTEGER,json_data:JSON

bq show mydataset.table1

Last modified          Schema         Total Rows   Total Bytes
----------------- -------------------- ------------ -------------
 22 Dec 22:10:32   |- id: integer       3            63
                   |- json_data: json

줄바꿈으로 구분된 JSON 파일에서 로드

다음 예시에서는 다음 레코드를 포함하는 file1.jsonl이라는 파일이 있다고 가정합니다.

{"id": 1, "json_data": 20}
{"id": 2, "json_data": "This is a string"}
{"id": 3, "json_data": {"id": 10, "name": "Alice"}}

bq 명령줄 도구를 사용하여 이 파일을 로드하려면 bq load 명령어를 실행합니다.

bq load --source_format=NEWLINE_DELIMITED_JSON mydataset.table1 file1.jsonl id:INTEGER,json_data:JSON

bq show mydataset.table1

Last modified          Schema         Total Rows   Total Bytes
----------------- -------------------- ------------ -------------
 22 Dec 22:10:32   |- id: integer       3            63
                   |- json_data: json

Storage Write API 사용

Storage Write API를 사용하여 JSON 데이터를 수집할 수 있습니다. 다음 예에서는 Storage Write API Python 클라이언트를 사용하여 JSON 데이터 유형 열이 있는 테이블에 데이터를 씁니다.

직렬화된 스트리밍 데이터를 저장할 프로토콜 버퍼를 정의합니다. JSON 데이터는 문자열로 인코딩됩니다. 다음 예시에서 json_col 필드에는 JSON 데이터가 포함되어 있습니다.

message SampleData {
  optional string string_col = 1;
  optional int64 int64_col = 2;
  optional string json_col = 3;
}

각 행의 JSON 데이터 형식을 STRING 값으로 지정합니다.

row.json_col = '{"a": 10, "b": "bar"}'
row.json_col = '"This is a string"' # The double-quoted string is the JSON value.
row.json_col = '10'

코드 예시와 같이 행을 쓰기 스트림에 추가합니다. 클라이언트 라이브러리는 프로토콜 버퍼 형식으로의 직렬화를 처리합니다.

수신되는 JSON 데이터의 형식을 지정할 수 없는 경우 코드에서 json.dumps() 메서드를 사용해야 합니다. 예를 들면 다음과 같습니다.

import json

...

row.json_col = json.dumps({"a": 10, "b": "bar"})
row.json_col = json.dumps("This is a string") # The double-quoted string is the JSON value.
row.json_col = json.dumps(10)

...

기존 스트리밍 API 사용

다음 예에서는 로컬 파일에서 JSON 데이터를 로드하고 기존 스트리밍 API를 사용하여 json_data라는 JSON 데이터 유형 열이 있는 BigQuery 테이블로 스트리밍합니다.

from google.cloud import bigquery
import json

# TODO(developer): Replace these variables before running the sample.
project_id = 'MY_PROJECT_ID'
table_id = 'MY_TABLE_ID'

client = bigquery.Client(project=project_id)
table_obj = client.get_table(table_id)

# The column json_data is represented as a JSON data-type column.
rows_to_insert = [
    {"id": 1, "json_data": 20},
    {"id": 2, "json_data": "This is a string"},
    {"id": 3, "json_data": {"id": 10, "name": "Alice"}}
]

# If the column json_data is represented as a String data type, modify the rows_to_insert values:
#rows_to_insert = [
#    {"id": 1, "json_data": json.dumps(20)},
#    {"id": 2, "json_data": json.dumps("This is a string")},
#    {"id": 3, "json_data": json.dumps({"id": 10, "name": "Alice"})}
#]

# Throw errors if encountered.
# https://cloud.google.com/python/docs/reference/bigquery/latest/google.cloud.bigquery.client.Client#google_cloud_bigquery_client_Client_insert_rows

errors = client.insert_rows(table=table_obj, rows=rows_to_insert)
if errors == []:
    print("New rows have been added.")
else:
    print("Encountered errors while inserting rows: {}".format(errors))

자세한 내용은 BigQuery로 데이터 스트리밍을 참조하세요.

JSON 데이터 쿼리

이 섹션에서는 GoogleSQL을 사용하여 JSON에서 값을 추출하는 방법을 설명합니다. JSON은 대소문자를 구분하며 필드 및 값 모두에서 UTF-8을 지원합니다.

이 섹션의 예시에서는 다음 표를 사용합니다.

CREATE OR REPLACE TABLE mydataset.table1(id INT64, cart JSON);

INSERT INTO mydataset.table1 VALUES
(1, JSON """{
        "name": "Alice",
        "items": [
            {"product": "book", "price": 10},
            {"product": "food", "price": 5}
        ]
    }"""),
(2, JSON """{
        "name": "Bob",
        "items": [
            {"product": "pen", "price": 20}
        ]
    }""");

값을 JSON으로 추출

BigQuery에서 JSON 유형을 사용할 때 필드 액세스 연산자를 사용하여 JSON표현식의 필드에 액세스할 수 있습니다. 다음 예시에서는 cart 열의 name 필드를 반환합니다.

SELECT cart.name
FROM mydataset.table1;
 
+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

배열 요소에 액세스하려면 JSON 하위 스크립트 연산자를 사용합니다. 다음 예시에서는 items 배열의 첫 번째 요소를 반환합니다.

SELECT
  cart.items[0] AS first_item
FROM mydataset.table1
 
+-------------------------------+
|          first_item           |
+-------------------------------+
| {"price":10,"product":"book"} |
| {"price":20,"product":"pen"}  |
+-------------------------------+

JSON 하위 스크립트 연산자를 사용하여 이름으로 JSON 객체의 멤버를 참조할 수도 있습니다.

SELECT cart['name']
FROM mydataset.table1;
 
+---------+
|  name   |
+---------+
| "Alice" |
| "Bob"   |
+---------+

하위 스크립팅 작업의 경우 괄호 안의 표현식은 상수가 아닌 표현식을 포함한 임의의 문자열 또는 정수 표현식일 수 있습니다.

DECLARE int_val INT64 DEFAULT 0;

SELECT
  cart[CONCAT('it','ems')][int_val + 1].product AS item
FROM mydataset.table1;
 
+--------+
|  item  |
+--------+
| "food" |
| NULL   |
+--------+

필드 액세스 및 하위 스크립트 연산자는 모두 JSON 유형을 반환하므로, 이를 사용하는 표현식을 연결하거나 JSON 유형을 사용하는 다른 함수에 결과를 전달할 수 있습니다.

이러한 연산자는 JSON_QUERY 함수의 슈가 구문입니다. 예를 들어 cart.name 표현식은 JSON_QUERY(cart, "$.name")과 동일합니다.

JSON 객체에 지정된 이름의 구성원이 없거나 JSON 배열에 지정된 위치의 요소가 없는 경우 이러한 연산자는 SQL NULL을 반환합니다.

SELECT
  cart.address AS address,
  cart.items[1].price AS item1_price
FROM
  mydataset.table1;
 
+---------+-------------+
| address | item1_price |
+---------+-------------+
| NULL    | NULL        |
| NULL    | 5           |
+---------+-------------+

등호 연산자와 비교 연산자는 JSON 데이터 유형에 정의되어 있지 않습니다. 따라서 GROUP BY 또는 ORDER BY와 같은 절에서는 JSON 값을 직접 사용할 수 없습니다. 대신 JSON_VALUE 함수를 사용하여 다음 섹션에 설명된 대로 필드 값을 SQL 문자열로 추출합니다.

값을 문자열로 추출

JSON_VALUE 함수는 스칼라 값을 추출하여 SQL 문자열로 반환합니다. cart.name이 JSON의 스칼라 값을 가리키지 않으면 SQL NULL을 반환합니다.

SELECT JSON_VALUE(cart.name) AS name
FROM mydataset.table1;
 
+-------+
| name  |
+-------+
| Alice |
+-------+

WHERE 절이나 GROUP BY 절과 같이 동등이나 비교가 필요한 컨텍스트에서는 JSON_VALUE 함수를 사용할 수 있습니다. 다음 예시에서는 JSON 값으로 필터링하는 WHERE 절을 보여줍니다.

SELECT
  cart.items[0] AS first_item
FROM
  mydataset.table1
WHERE
  JSON_VALUE(cart.name) = 'Alice';
 
+-------------------------------+
| first_item                    |
+-------------------------------+
| {"price":10,"product":"book"} |
+-------------------------------+

또는 JSON 문자열을 추출하고 해당 값을 SQL STRING으로 반환하는 STRING 함수를 사용할 수 있습니다. 예를 들면 다음과 같습니다.

SELECT STRING(JSON '"purple"') AS color;
 
+--------+
| color  |
+--------+
| purple |
+--------+

STRING 외에도 JSON 값을 추출하고 이를 다른 SQL 데이터 유형으로 반환해야 할 수 있습니다. 다음 값 추출 함수를 사용할 수 있습니다.

JSON 값 유형을 가져오려면 JSON_TYPE 함수를 사용하면 됩니다.

유연하게 JSON 변환

LAX Conversion 함수를 사용하여 JSON 값을 오류 없이 유연하게 스칼라 SQL 값으로 변환할 수 있습니다.

다음 예시에서는 이러한 함수의 성능을 보여줍니다. LAX_IN64는 자동으로 입력을 올바르게 추론하고 처리합니다. 

SELECT LAX_INT64(JSON '"10"') AS id;
 
+----+
| id |
+----+
| 10 |
+----+

LAX_IN64 외에도 다음 함수를 사용하여 다른 SQL 유형을 JSON으로 유연하게 변환할 수 있습니다.

JSON에서 배열 추출

JSON은 BigQuery의 ARRAY<JSON> 유형과 직접 일치하지 않는 JSON 배열을 포함할 수 있습니다. 다음 함수를 사용하여 JSON에서 BigQuery ARRAY를 추출할 수 있습니다.

  • JSON_QUERY_ARRAY: 배열을 추출하고 JSON의 ARRAY<JSON>으로 반환합니다.
  • JSON_VALUE_ARRAY: 스칼라 값의 배열을 추출하여 스칼라 값의 ARRAY<STRING>으로 반환합니다.

다음 예시에서는 JSON_QUERY_ARRAY를 사용하여 JSON 배열을 추출합니다.

SELECT JSON_QUERY_ARRAY(cart.items) AS items
FROM mydataset.table1;
 
+----------------------------------------------------------------+
| items                                                          |
+----------------------------------------------------------------+
| [{"price":10,"product":"book"}","{"price":5,"product":"food"}] |
| [{"price":20,"product":"pen"}]                                 |
+----------------------------------------------------------------+

배열을 개별 요소로 분할하려면 배열의 각 요소에 대한 행이 한 개씩 포함된 테이블을 반환하는 UNNEST 연산자를 사용합니다. 다음 예시에서는 items 배열의 각 구성원에서 product 구성원을 선택합니다.

SELECT
  id,
  JSON_VALUE(item.product) AS product
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
ORDER BY id;
 
+----+---------+
| id | product |
+----+---------+
|  1 | book    |
|  1 | food    |
|  2 | pen     |
+----+---------+

다음 예시는 비슷하지만 ARRAY_AGG 함수를 사용하여 값을 다시 SQL 배열로 집계합니다.

SELECT
  id,
  ARRAY_AGG(JSON_VALUE(item.product)) AS products
FROM
  mydataset.table1, UNNEST(JSON_QUERY_ARRAY(cart.items)) AS item
GROUP BY id
ORDER BY id;
 
+----+-----------------+
| id | products        |
+----+-----------------+
|  1 | ["book","food"] |
|  2 | ["pen"]         |
+----+-----------------+

배열에 대한 자세한 내용은 GoogleSQL의 배열 작업을 참조하세요.

JSON null

JSON 유형에는 SQL NULL과 다른 특별한 null 값이 있습니다. JSON null은 다음 예시와 같이 SQL NULL 값으로 취급되지 않습니다.

SELECT JSON 'null' IS NULL;
 
+-------+
| f0_   |
+-------+
| false |
+-------+

null 값으로 JSON 필드를 추출할 때는 동작이 함수에 따라 달라집니다.

  • 유효한 JSON 값이므로 JSON_QUERY 함수는 JSON null을 반환합니다.
  • JSON null은 스칼라 값이 아니므로 JSON_VALUE 함수는 SQL NULL을 반환합니다.

다음 예시에서는 다양한 동작을 보여줍니다.

SELECT
  json.a AS json_query, -- Equivalent to JSON_QUERY(json, '$.a')
  JSON_VALUE(json, '$.a') AS json_value
FROM (SELECT JSON '{"a": null}' AS json);
 
+------------+------------+
| json_query | json_value |
+------------+------------+
| null       | NULL       |
+------------+------------+