programming/SQL

JSON 데이터 추출하는 방법 (json_extract, json_array 함수 등)

Jofresh 2023. 5. 26. 20:00
728x90
반응형

JSON에서 데이터 추출

Athena의 테이블로 역직렬화할 필요가 없는 JSON 인코딩 문자열을 포함하는 원본 데이터가 있을 수 있습니다. 이 경우에도 Presto에 제공된 JSON 함수를 사용하여 이 데이터에 대해 SQL 작업을 실행할 수 있습니다.

아래 JSON 문자열을 예제 데이터 세트로 간주합니다.

{"name": "Susan Smith",
"org": "engineering",
"projects":
    [
     {"name":"project1", "completed":false},
     {"name":"project2", "completed":true}
    ]
}

예제: 속성 추출

JSON 문자열에서 name 및 projects 속성을 추출하려면 다음 예제에서와 같이 json_extract 함수를 사용합니다. json_extract 함수는 JSON 문자열을 포함하는 열을 가져오고 JSONPath처럼 점 . 표기법으로 이루어진 식을 검색합니다.

 

WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},
           {"name":"project2", "completed":true}]}'
    AS blob
)
SELECT
  json_extract(blob, '$.name') AS name,
  json_extract(blob, '$.projects') AS projects
FROM dataset

반환되는 값은 기본 Athena 데이터 유형이 아닌 JSON 인코딩 문자열입니다.

 

+-----------------------------------------------------------------------------------------------+
| name           | projects                                                                     |
+-----------------------------------------------------------------------------------------------+
| "Susan Smith"  | [{"name":"project1","completed":false},{"name":"project2","completed":true}] |
+-----------------------------------------------------------------------------------------------+

JSON 문자열에서 스칼라 값을 추출하려면 json_extract_scalar 함수를 사용합니다. json_extract와 비슷하지만 스칼라 값(부울, 숫자 또는 문자열)만 반환합니다.

 

 

참고
어레이, 맵 또는 구조체에 json_extract_scalar 함수를 사용하지 마세요.
WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS blob
)
SELECT
  json_extract_scalar(blob, '$.name') AS name,
  json_extract_scalar(blob, '$.projects') AS projects
FROM dataset

이 쿼리가 반환하는 값:

 

+---------------------------+
| name           | projects |
+---------------------------+
| Susan Smith    |          |
+---------------------------+

예제 어레이에서 projects 속성의 첫 번째 요소를 얻으려면 json_array_get 함수를 사용하고 인덱스 위치를 지정합니다.

 

WITH dataset AS (
  SELECT '{"name": "Bob Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS blob
)
SELECT json_array_get(json_extract(blob, '$.projects'), 0) AS item
FROM dataset

JSON 인코딩 어레이에서 지정된 인덱스 위치에 있는 값을 반환합니다.

+---------------------------------------+
| item                                  |
+---------------------------------------+
| {"name":"project1","completed":false} |
+---------------------------------------+

Athena 문자열 유형이 반환되게 하려면 JSONPath 식 내에 [] 연산자를 사용한 다음 json_extract_scalar 함수를 사용합니다.

 

WITH dataset AS (
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
     AS blob
)
SELECT json_extract_scalar(blob, '$.projects[0].name') AS project_name
FROM dataset

다음 결과를 반환합니다.

+--------------+
| project_name |
+--------------+
| project1     |
+--------------+

 

 

JSON 배열에서 값 검색

 

특정 값이 JSON 인코딩 배열 내에 있는지 알아보려면 json_array_contains 함수를 사용합니다.

다음 쿼리는 "project2"에 참여하는 사용자의 이름을 나열합니다.

 

WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith", "org": "legal", "projects": ["project1"]}'),
    (JSON '{"name": "Susan Smith", "org": "engineering", "projects": ["project1", "project2", "project3"]}'),
    (JSON '{"name": "Jane Smith", "org": "finance", "projects": ["project1", "project2"]}')
  ) AS t (users)
)
SELECT json_extract_scalar(users, '$.name') AS user
FROM dataset
WHERE json_array_contains(json_extract(users, '$.projects'), 'project2')

 

이 쿼리는 사용자 목록을 반환합니다.

+-------------+
| user        |
+-------------+
| Susan Smith |
+-------------+
| Jane Smith  |
+-------------+

다음 쿼리 예제는 완료된 프로젝트의 총 개수와 함께 프로젝트를 완료한 사용자의 이름을 나열합니다. 그리고 이러한 작업을 수행합니다.

  • 명확성을 위해 중첩 SELECT 설명을 사용합니다.
  • 프로젝트의 배열을 추출합니다.
  • CAST를 사용하여 배열을 키-값 페어의 기본 배열로 변환합니다.
  • UNNEST 연산자를 사용하여 각각의 배열 요소를 추출합니다.
  • 획득한 값을 완료된 프로젝트별로 필터링하고 개수를 셉니다.

 

참고
MAP에 CAST를 사용할 때 키 요소를 VARCHAR(Presto의 기본 문자열)로 지정할 수 있지만 값은 JSON으로 남겨 둡니다. MAP의 값 유형이 서로 다르기 때문입니다(첫 번째 키-값 페어는 문자열, 두 번째는 부울).
WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith",
             "org": "legal",
             "projects": [{"name":"project1", "completed":false}]}'),
    (JSON '{"name": "Susan Smith",
             "org": "engineering",
             "projects": [{"name":"project2", "completed":true},
                          {"name":"project3", "completed":true}]}'),
    (JSON '{"name": "Jane Smith",
             "org": "finance",
             "projects": [{"name":"project2", "completed":true}]}')
  ) AS t (users)
),
employees AS (
  SELECT users, CAST(json_extract(users, '$.projects') AS
    ARRAY(MAP(VARCHAR, JSON))) AS projects_array
  FROM dataset
),
names AS (
  SELECT json_extract_scalar(users, '$.name') AS name, projects
  FROM employees, UNNEST (projects_array) AS t(projects)
)
SELECT name, count(projects) AS completed_projects FROM names
WHERE cast(element_at(projects, 'completed') AS BOOLEAN) = true
GROUP BY name

이 쿼리는 다음 결과를 반환합니다.

+----------------------------------+
| name        | completed_projects |
+----------------------------------+
| Susan Smith | 2                  |
+----------------------------------+
| Jane Smith  | 1                  |
+----------------------------------+

 

 

JSON 배열의 길이와 크기 획득

예: json_array_length

JSON 인코딩 배열의 길이를 얻으려면 json_array_length 함수를 사용합니다.

WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name":
            "Bob Smith",
            "org":
            "legal",
            "projects": [{"name":"project1", "completed":false}]}'),
    (JSON '{"name": "Susan Smith",
            "org": "engineering",
            "projects": [{"name":"project2", "completed":true},
                         {"name":"project3", "completed":true}]}'),
    (JSON '{"name": "Jane Smith",
             "org": "finance",
             "projects": [{"name":"project2", "completed":true}]}')
  ) AS t (users)
)
SELECT
  json_extract_scalar(users, '$.name') as name,
  json_array_length(json_extract(users, '$.projects')) as count
FROM dataset
ORDER BY count DESC

이 쿼리는 다음 결과를 반환합니다.

+---------------------+
| name        | count |
+---------------------+
| Susan Smith | 2     |
+---------------------+
| Bob Smith   | 1     |
+---------------------+
| Jane Smith  | 1     |
+---------------------+

 

예: json_size

JSON 인코딩 배열이나 객체의 크기를 얻으려면 json_size 함수를 사용하고 JSON 문자열과 JSONPath 표현식이 포함된 열을 배열이나 객체에 지정합니다.

WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith", "org": "legal", "projects": [{"name":"project1", "completed":false}]}'),
    (JSON '{"name": "Susan Smith", "org": "engineering", "projects": [{"name":"project2", "completed":true},{"name":"project3", "completed":true}]}'),
    (JSON '{"name": "Jane Smith", "org": "finance", "projects": [{"name":"project2", "completed":true}]}')
  ) AS t (users)
)
SELECT
  json_extract_scalar(users, '$.name') as name,
  json_size(users, '$.projects') as count
FROM dataset
ORDER BY count DESC

이 쿼리는 다음 결과를 반환합니다.

+---------------------+
| name        | count |
+---------------------+
| Susan Smith | 2     |
+---------------------+
| Bob Smith   | 1     |
+---------------------+
| Jane Smith  | 1     |
+---------------------+
728x90
반응형

'programming > SQL' 카테고리의 다른 글

[SQL] left join과 full outer join의 차이점  (0) 2024.03.13
SQL COUNT, CASE WHEN함수 함께 사용하기  (0) 2023.07.17
[SQL]17강_GROUP BY  (0) 2023.05.08
[SQL]16강_UNION  (0) 2023.05.01
[SQL]15강_FULL OUTER JOIN  (0) 2023.05.01

'programming/SQL'의 다른글

  • 현재글JSON 데이터 추출하는 방법 (json_extract, json_array 함수 등)

관련글

티스토리툴바