json data에 대한 압축을 푸는 트리거를 만들려면 어떻게 해야 합니까?

json data에 대한 압축을 푸는 트리거를 만들려면 어떻게 해야 합니까?

그런 다음 아래 코드를 WooCommerce 템플릿 파일 리뷰에 추가해야 합니다.php 또는 woocmerce_review_before_comment_meta hook을 사용할 수 있지만, 제 경우에는 그 코드를 작성했습니다.json_table나는 이것들을 재귀적 CTE로 추출하고 있습니다.

create or replace table base_data(
    id int auto_increment not null primary key,
    record_data longtext not null check (json_valid(record_data))
);

insert into base_data(record_data)
values
('{"x":1, "y":[2,3,4]}')
;

create or replace table mv_integers as
with recursive array_values as (
    select 
        json_extract(b.record_data, '$.y[*]') val
        , b.id
        , json_length(json_extract(b.record_data, '$.y[*]')) n
        from base_data b
), rec_elems as (
    select av.id, 0 as i, json_extract(av.val, '$[0]') elem
    from array_values av
    where av.n > 0
    union all
    select av.id, e.i + 1, json_extract(av.val, concat('$[', e.i + 1, ']'))
    from array_values av
        inner join rec_elems e on av.id = e.id
    where (i + 1) < av.n
)
select r.id record_id, r.elem extracted_integer, 'y' as 'comes_from'
from rec_elems r
union all
select b.id, json_value(b.record_data, '$.x'), 'x'
from base_data b
;

create or replace trigger maintain_mv_integers
after insert on base_data for each row 
insert mv_integers(record_id, extracted_integer, comes_from)
with recursive array_values as (
    select 
        json_extract(new.record_data, '$.y[*]') val
        , new.id
        , json_length(json_extract(new.record_data, '$.y[*]')) n
), rec_elems as (
    select av.id, 0 as i, json_extract(av.val, '$[0]') elem
    from array_values av
    where av.n > 0
    union all
    select av.id, e.i + 1, json_extract(av.val, concat('$[', e.i + 1, ']'))
    from array_values av
        inner join rec_elems e on av.id = e.id
    where (i + 1) < av.n
)
select r.id record_id, r.elem extracted_integer, 'y' as 'comes_from'
from rec_elems r
union all
select new.id, json_value(new.record_data, '$.x'), 'x'
;

insert into base_data(record_data)
values
('{"x":5, "y":[6,7,4]}')
;

코드가 수행하는 작업:

1. 테이블 만들기
2. 해당 테이블을 일부 데이터로 채웁니다.
3. 해당 데이터에서 구체화된 뷰 생성
4. 형상화된 뷰를 앞으로 유지하기 위한 트리거 만들기
5. 트리거가 작동하는지 확인하기 위해 새 데이터를 삽입합니다.

마지막 단계에서 코드가 실패합니다.오류는Unknown column 'record_data' in 'NEW'. 제가 조금 전에 댓글을 달면.union allCTE가 포함되지 않은 마지막 비트만 남겨두면 코드가 작동합니다.트리거에서 CTE가 지원되지 않는다는 의미입니까?그렇지 않기를 바라며 이 결론을 뒷받침하는 어떤 문서도 찾을 수 없습니다.

제 버전은.10.5.8-MariaDB-1:10.5.8+maria~bionicWSL2 Ubuntu에서 실행됩니다.

그럼 어떻게 하면 이걸 작동시킬 수 있을까요?

저는 해결책을 생각해 냈습니다.제가 한 일은 트리거의 논리를 저장된 절차에 넣고 트리거에서 호출하는 것뿐이었습니다.이것은 작동하지만 원본은 작동하지 않는다는 사실은 이것이 버그나 문서화되지 않은 행동임을 암시합니다.

업데이트: 이제 확인된 버그입니다.

create or replace table base_data(
    id int auto_increment not null primary key,
    record_data longtext not null check (json_valid(record_data))
);

insert into base_data(record_data)
values
('{"x":1, "y":[2,3,4]}')
;

create or replace table mv_integers as
with recursive array_values as (
    select 
        json_extract(b.record_data, '$.y[*]') val
        , b.id
        , json_length(json_extract(b.record_data, '$.y[*]')) n
        from base_data b
), rec_elems as (
    select av.id, 0 as i, json_extract(av.val, '$[0]') elem
    from array_values av
    where av.n > 0
    union all
    select av.id, e.i + 1, json_extract(av.val, concat('$[', e.i + 1, ']'))
    from array_values av
        inner join rec_elems e on av.id = e.id
    where (i + 1) < av.n
)
select r.id record_id, r.elem extracted_integer, 'y' as 'comes_from'
from rec_elems r
union all
select b.id, json_value(b.record_data, '$.x'), 'x'
from base_data b
;

create or replace procedure sp_maintain_mv_integers(
    in record_id int unsigned,
    in json_data longtext 
)
insert mv_integers(record_id, extracted_integer, comes_from)
with recursive array_values as (
    select 
        json_extract(json_data, '$.y[*]') val
        , record_id id
        , json_length(json_extract(json_data, '$.y[*]')) n
), rec_elems as (
    select av.id, 0 as i, json_extract(av.val, '$[0]') elem
    from array_values av
    where av.n > 0
    union all
    select av.id, e.i + 1, json_extract(av.val, concat('$[', e.i + 1, ']'))
    from array_values av
        inner join rec_elems e on av.id = e.id
    where (i + 1) < av.n
)
select r.id record_id, r.elem extracted_integer, 'y' as 'comes_from'
from rec_elems r
union all
select record_id, json_value(json_data, '$.x'), 'x';


create or replace trigger tg_maintain_mv_integers
after insert on base_data for each row 
call sp_maintain_mv_integers(new.id, new.record_data);

insert into base_data(record_data)
values
('{"x":5, "y":[6,7,4]}')
;

언급URL : https://stackoverflow.com/questions/65431349/how-can-i-create-a-trigger-to-unpack-json-data