인프런 커뮤니티 질문&답변

안녕하세요?

올려주신 형태의 데이터는 배열 형태의 JSON 문자열이 아닙니다. 다음과 같은 형태가 되어야 합니다.

{
"fruit": [{"name": "사과", "price": 2000}, 
	  {"name": "배", "price": 2000}],
"food": [{"name": "햄버거", "price": 7500}]
}

그래야 여러 과일을 반복해 읽어올 수 있습니다.

그리고 제가 경험한 바로는 MySQL에서는 JSON의 속성 이름이 한글이 되면 오류가 발생합니다. 그래서 위와 같이 영문으로 이름을 주는 것이 좋습니다. 제대로 된 JSON 형태라면 JSON 관련 함수를 사용해 조회할 수 있습니다. 우선 쉽게 조회할 수 있는 형태로 테이터를 전달 받을 수 있는 방법을 고민해 보시면 좋을 것 같습니다.

우선 다음 예제 코드를 참고하시기 바랍니다.

-- 테이블 만들기
CREATE TABLE Sample (
    id INT AUTO_INCREMENT PRIMARY KEY,
    json_data JSON
);

-- 데이터 추가
INSERT INTO Sample (json_data)
VALUES ('
{
"fruit": [{"name": "사과", "price": 2000}, 
	  {"name": "배", "price": 2000}],
"food": [{"name": "햄버거", "price": 7500}]
}
');

-- 추출
SELECT 이름, 가격 FROM (
    SELECT jt.이름, jt.가격
	FROM sample,
	JSON_TABLE(json_data, '$.fruit[*]' 
	COLUMNS (이름 VARCHAR(100) PATH '$.name',
	         가격 INT PATH '$.price')) jt
    UNION ALL

    SELECT jt.이름, jt.가격
	FROM sample,
	JSON_TABLE(json_data, '$.food[*]' 
        COLUMNS (이름 VARCHAR(100) PATH '$.name',
                 가격 INT PATH '$.price')) jt
) AS combined;

-- 결과 
/*
이름    가격
-------------
사과    2000
배      2000
햄버거  7500
*/

감사합니다.

안녕하세요, 인프런 AI 인턴입니다. hunter님

JSON 배열 파싱은 SQL에서 직접적으로 수행하기 어려운 경우가 많습니다. 하지만, 특정 DBMS에서는 이러한 작업을 지원하는 함수들을 제공하고 있습니다. 질문하신 내용에 기반하여 a라는 컬럼에 JSON 데이터를 어떻게 파싱하고 조작할 수 있는지 몇 가지 방법을 소개하겠습니다.

MySQL의 경우, JSON 데이터를 다루기 위해 JSON 함수를 사용할 수 있습니다. 여기에서는 JSON_EXTRACT와 같은 함수를 활용하여 특정 키의 값을 추출할 수 있습니다. 아쉽게도 예시로 보여주신 JSON 데이터가 형식적으로 잘못된 상태이기 때문에 직접적으로 사용할 수 없습니다. JSON 형식에서 같은 키(예: “과일”)가 여러 번 등장하여 안타깝게도 정상적으로 파싱되지 않습니다.

다음은 개선된 접근 방법입니다:

1. JSON 데이터 형식 수정

JSON의 형식을 다음처럼 수정해야 합니다. 같은 키를 여러 번 사용하면 안 되므로 중첩 구조를 변경합니다.

{
  "items": [
    { "type": "과일", "이름": "사과", "가격": 2000 },
    { "type": "음식", "이름": "햄버거", "가격": 7500 }
  ]
}

2. JSON 함수 사용

이를 바탕으로 SQL quer를 작성하여 JSON 데이터를 추출할 수 있습니다:

SELECT JSON_UNQUOTE(JSON_EXTRACT(a, '$.items[*].이름')) AS 이름,
       JSON_UNQUOTE(JSON_EXTRACT(a, '$.items[*].가격')) AS 가격
FROM your_table;

이 예시에서는 ‘items’라는 키 아래 중첩된 JSON 배열의 요소들을 추출합니다.

추가 팁

• PostgreSQL: PostgreSQL에서는 jsonb_array_elements를 사용하여 배열 내의 각 요소를 별도의 행으로 분해할 수 있습니다.
  
• Oracle: json_table을 통해 JSON 데이터로 가공작업을 수행할 수 있습니다.
  

보다 자세한 사용법은 각 DBMS의 공식 문서를 참고하시길 권장드립니다. 다양한 데이터베이스마다 JSON 데이터에 대한 접근 방법이 다를 수 있습니다.

JSON Path Expressions을 사용하여 원하는 값을 추출하시면 도움이 될 것입니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.