데이터베이스 질문과 답변
<문제>
* 테이블명 : order_regular_delivery_schdule
* 칼럼명 : meal
* meal 칼럼의 예시 레코드는 아래와 같습니다.
[{"set_cnt":1,"set_meal":[{"day_of_week":"thu","pid":"0000010010","pname":"애호박죽","pimg":"https://foodcare-cle.com/data/mall_data/images/product/00/00/01/00/10/m_0000010010.gif?1676538858","al_matter":[""],"is_care":false,"qty":1,"group":"2023-02-2343010_10","start_group":true,"end_group":false,"max_qty":"1"}]},{"set_cnt":1,"set_meal":[{"day_of_week":"wed","pid":"0000010014","pname":"현미죽","pimg":"https://foodcare-cle.com/data/mall_data/images/product/00/00/01/00/14/m_0000010014.gif?1676538858","al_matter":[""],"is_care":false,"qty":1,"group":"2023-03-0134110_10","start_group":true,"end_group":false,"max_qty":"1"}]}]
* 제가 작성한 쿼리 : SELECT JSON_EXTRACT(d.meal, '$[*].set_cnt') FROM order_regular_delivery_schdule AS d
* 이에 대한 결괏값으로는 예를 들어,
첫번째 행(위에 예시로 올려드린 레코드) 에는 [1,1]
두번째 행에는 [1]
세번째 행에는 [1,2,1] 이런식으로 반환됩니다.
<원하는 점>
저는 여기서 [1]이나 [1,1] 그리고 [1,2,1] 같은 리스트의 리스트 내 요소들의 합계를 구하는 쿼리를 작성하고 싶은데,
여기서 SELECT SUM(JSON_EXTRACT(d.meal, '$[*].set_cnt')) FROM order_regular_delivery_schdule AS d
이런식으로 SUM()함수를 사용하면
각 행별로 리스트의 합계가 추출되지 않고,
모든 행을 합해버린뒤 하나의 행으로만 결괏값이 도출됩니다.
<질문>
1. 제가 원하는 대로 각 행별로 합계를 구하는 방법이 있을까요?
2. 그리고 각 행별로 요소들의 개수를 count 하는 방법이 있을까요?
sql 쿼리 초보라 많은 도움 주시면 감사하겠습니다.
1. 그룹바이 하면 됩니다.
2. JSON_LENGTH
3. 사용하신 SUM 방식으로는 원하는 결과가 나오지 않을 듯 합니다. -> 개별로 쪼갠 후에 SUM 해야 할 듯.
WITH order_regular_delivery_schdule AS
(
SELECT 1 idx, '[{"set_cnt":1,"set_meal":[{"day_of_week":"thu","pid":"0000010010","pname":"애호박죽","pimg":"https://foodcare-cle.com/data/mall_data/images/product/00/00/01/00/10/m_0000010010.gif?1676538858","al_matter":[""],"is_care":false,"qty":1,"group":"2023-02-2343010_10","start_group":true,"end_group":false,"max_qty":"1"}]},{"set_cnt":1,"set_meal":[{"day_of_week":"wed","pid":"0000010014","pname":"현미죽","pimg":"https://foodcare-cle.com/data/mall_data/images/product/00/00/01/00/14/m_0000010014.gif?1676538858","al_matter":[""],"is_care":false,"qty":1,"group":"2023-03-0134110_10","start_group":true,"end_group":false,"max_qty":"1"}]}]' meal
UNION ALL SELECT 2, '[{"set_cnt":1,"set_meal":[{"day_of_week":"thu","pid":"0000010010","pname":"애호박죽","pimg":"https://foodcare-cle.com/data/mall_data/images/product/00/00/01/00/10/m_0000010010.gif?1676538858","al_matter":[""],"is_care":false,"qty":1,"group":"2023-02-2343010_10","start_group":true,"end_group":false,"max_qty":"1"}]}]'
)
, copy_t AS
(
SELECT 0 lv
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
)
SELECT idx
, JSON_EXTRACT(d.meal, '$[*].set_cnt') set_cnt
, SUM(JSON_EXTRACT(d.meal, CONCAT('$[', lv, '].set_cnt'))) set_cnt_sum
FROM order_regular_delivery_schdule d
INNER JOIN copy_t c
ON c.lv < JSON_LENGTH(d.meal)
GROUP BY idx
, JSON_EXTRACT(d.meal, '$[*].set_cnt')
;
) 버튼을 클릭하여 작성 하시면 됩니다.