Bug #89366 Provide a JSON function analogous to Postgres row_to_json
Submitted: 23 Jan 2018 21:51 Modified: 13 Feb 2018 7:14
Reporter: Jonathan Stewmon Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[23 Jan 2018 21:51] Jonathan Stewmon
I would like to be able to convert a row to JSON without having to format the query using JSON_OBJECT with an explicit list of fields and values. Postgres' supports this feature with its row_to_json function.

In most cases, using JSON_OBJECT is sufficient, but I'd like to be able to convert rows in trigger statements to JSON, so that I can capture data changes in a generic audit table.

With the current capabilities of triggers and JSON functions, it isn't possible to define a generic trigger procedure that can capture the row contents as JSON. We can use JSON_OBJECT to create the JSON, but this requires the trigger to explicitly list all of the row's fields, since dynamic sql is prohibited in triggers.

Furthermore, when columns are added to or removed from a table, the trigger must be modified accordingly. In the case of a column being added, we must wait until the new column is defined before the trigger can be dropped and recreated to avoid errors from the trigger referencing a column that does not exist. 

How to repeat:
This is a feature request.
[13 Feb 2018 7:14] MySQL Verification Team
Hello Jonathan,

Thank you for the feature request!