MySQL Bugs: #116731: Allow creating JSON_OBJECT in trigger from NEW/OLD

Bug #116731	Allow creating JSON_OBJECT in trigger from NEW/OLD
Submitted:	20 Nov 2024 14:15	Modified:	20 Nov 2024 15:58
Reporter:	Oleg Andreyev	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi!

It would be great if MySQL could support creating JSON from NEW/OLD keywords.

Case: I want to create audit records on each insert/update/delete and record everything that has changed.

Currently it's impossible to use NEW.*, OLD.* in JSON_OBJECT (or anyother function)

Example from PG:

```
create or replace function changelog_trigger() returns trigger as $$
                declare
                    action text;
                    table_name text;
                    transaction_id bigint;
                    timestamp timestamp;
                    old_data jsonb;
                    new_data jsonb;
                begin
                    action := lower(TG_OP::text);
                    table_name := TG_TABLE_NAME::text;
                    transaction_id := txid_current();
                    timestamp := current_timestamp;

                    if TG_OP = 'DELETE' then
                        old_data := to_jsonb(OLD.*);
                    elseif TG_OP = 'INSERT' then
                        new_data := to_jsonb(NEW.*);
                    elseif TG_OP = 'UPDATE' then
                        old_data := to_jsonb(OLD.*);
                        new_data := to_jsonb(NEW.*);
                    end if;

                insert into changelog (body)
                values (json_build_object('action', action, 'table_name', table_name, 'transaction_id', transaction_id, 'timestamp', timestamp, 'old_data', old_data, 'new_data', new_data)::text);

                return null;
                end;
```

How to repeat:
-

HI Mr. Andreyev,

Thank you for your feature request.

However, when you ask for a new feature, you should define it entirely in MySQL syntax and not use non-standard syntax of other products.

You should read our Reference Manual that you can find on https://dev.mysql.com and very precisely define your feature request based ONLY on the syntax that MySQL supports. 

We can not verify a feature request that is not based on the SQL Standard. So, please use SQL Standard 2016 and what it supports and try to define VERY precisely your feature request, based on MySQL features only.

Unsupported.

Updated my example to match MySQL Syntax as much as possible.

```sql
create trigger changelog_trigger
    after update
    on account
    for each row
BEGIN
    -- it would be great to create JSON from NEW or OLD variables in trigger, without "hardcoding" all columns
    set @old_data = JSON_OBJECT(OLD.*);
    set @new_data = JSON_OBJECT(NEW.*);
    
    insert into changelog (body) values (json_object('action', action, 'table_name', table_name, 'transaction_id', transaction_id, 'timestamp', timestamp, 'old_data', @old_data, 'new_data', @new_data));
END;
```

Hi Mr. Andreyev,

Can you try using JSON_FUNCTION() to achieve the same result ???

Please, check it out ........

To which function JSON_* you are referring to? https://dev.mysql.com/doc/refman/8.4/en/json-function-reference.html

Probably I can use information_schema to find all columns than concat everything to single string for 
JSON_OBJECT, and use prepare/execute inside trigger to get compiled result...

but looks not friendly for developers.

Sorry,

I misspelled it ...........

It is this one:

https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html#function_json-value

or any other on this page:

https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html

One important note ........

if your feature is not described in SQL Standard 2016, it is unlikely that it would be implemented.