i want to query my data in my database (i'm use mariadb version 10.6) this is my data

[{"STAFF_ID": 287, "DATE": "2022-03-30 14:54:37", "ACTION": "UPDATE", "TABLE": "m_course", "COLUMN": ["ID", "COURSE"] ,"OLD": [287, "Physics"], "NEW": [396,"Chemistry"]}]

and this is my query

    SELECT
    lg.ID,
    lg.MONTH_YEAR,
    P.NAMA,
    d.* 
   FROM
    log_akses_pegawai_json lg,
    json_table (
        lg.ACADEMIC,
        '$[*]' COLUMNS (
            id VARCHAR ( 10 ) path '$.STAFF_ID',
            date VARCHAR ( 20 ) path '$.DATE',
            action VARCHAR ( 20 ) path '$.ACTION',
            mytable VARCHAR ( 20 ) path '$.TABLE',
            nested path '$.COLUMN[*]' COLUMNS ( mycolumn VARCHAR ( 32 ) path '$' ),
            nested path '$.OLD[*]' COLUMNS ( old VARCHAR ( 32 ) path '$' ),
            nested path '$.NEW[*]' COLUMNS ( new VARCHAR ( 32 ) path '$' ) 
        )) AS d
    LEFT JOIN m_pegawai P ON P.ID_PEGAWAI = d.id;

and the result is

but i want to merge the record (merge the null value), because is must be just 2 record

CodePudding user response：

Because of the nested path, json explodes producing multiple rows. you will have to do a max value to dedup and eliminate nulls.

select ID, MONTH_YEAR, NAMA, max(id1), max(date), max(action), max(mytable), max(mycolumn), max(old), max(new)
from (
SELECT
    lg.ID,
    lg.MONTH_YEAR,
    P.NAMA,
    d.* 
   FROM
    log_akses_pegawai_json lg,
    json_table (
        lg.ACADEMIC,
        '$[*]' COLUMNS (
            id VARCHAR ( 10 ) path '$.STAFF_ID',
            date VARCHAR ( 20 ) path '$.DATE',
            action VARCHAR ( 20 ) path '$.ACTION',
            mytable VARCHAR ( 20 ) path '$.TABLE',
            nested path '$.COLUMN[*]' COLUMNS ( mycolumn VARCHAR ( 32 ) path '$' ),
            nested path '$.OLD[*]' COLUMNS ( old VARCHAR ( 32 ) path '$' ),
            nested path '$.NEW[*]' COLUMNS ( new VARCHAR ( 32 ) path '$' ) 
        )) AS d
    LEFT JOIN m_pegawai P ON P.ID_PEGAWAI = d.id)
group by ID, MONTH_YEAR, NAMA;

CodePudding user response：

• It's not smart to use * in such queries. Now you have two field that are named id. (that's why I added an alias for the first one to ID2)
• I also added rwc,rwo and rwn for ordinality. They produce the rownumber for the detail (see: ordinality columns
• The old-style join between lg.ACADEMIC and the JSON_TABLE is corrected (see: Bad Habits to Kick : Using old-style JOINs)

This produces:

Which can be queries using some INNER JOINs:

WITH result as(
 SELECT
    lg.ID as ID22,
    lg.MONTH_YEAR,
    P.NAMA,
    d.* 
   FROM
    log_akses_pegawai_json lg
    CROSS JOIN json_table(
        lg.ACADEMIC,
        '$[*]' COLUMNS (
            id VARCHAR ( 10 ) path '$.STAFF_ID',
            date VARCHAR ( 20 ) path '$.DATE',
            action VARCHAR ( 20 ) path '$.ACTION',
            mytable VARCHAR ( 20 ) path '$.TABLE',
            nested path '$.COLUMN[*]' COLUMNS ( rwc for ordinality, mycolumn VARCHAR ( 32 ) path '$' ),
            nested path '$.OLD[*]' COLUMNS ( rwo for ordinality, old VARCHAR ( 32 ) path '$' ),
            nested path '$.NEW[*]' COLUMNS ( rwn for ordinality, new VARCHAR ( 32 ) path '$' ) 
        )) AS d
    LEFT JOIN m_pegawai P ON P.ID_PEGAWAI = lg.ID
) 
select 
   rc.ID22 as ID,
   rc.MONTH_YEAR,
   rc.NAMA,
   rc.id,
   rc.`date`,
   rc.action,
   rc.mytable,
   rc.mycolumn,
   ro.old,
   rn.new
from result rc
inner join result ro on rc.rwc=ro.rwo
inner join result rn on rc.rwc=rn.rwn
where rc.rwc is not null
;

output:

DBFIDDLE

EDIT: The column NAMA is empty, this is due to incorrect data on my site. In the DBFIDDLE the column has data.