DMS - Is it possible to write an expression to get a row as JSON?

Question

I am using DMS and adding a column with a transformation rule. I want the value of that column to be the source row's contents as JSON.

Unfortunately, in [the documentation](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.html#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions-SQLite) it isn't clear to me what we can use from SQLite. (For example, `SELECT` statements throw a syntax error, but I can use some functions that are not listed in the documentation, so I don't know what I can and can't use. Also, `$` must be used to access columns which is not SQLite syntax, but is not explained so I don't know if there are other syntax differences I'm not aware of.)

I managed to get close to my goal with the following expression. It outputs what I want, but I can only get this by specifying each column name. My data is dynamic, so I need a way to generate this without knowing the column names.
```
"expression": "json_object('id', $id, 'name', $name, 'next_column', $next_column)"
```
If I could use all SQLite commands I could just do `.mode json` and ask for the row, but it seems this is not supported. How can I accomplish this?

Accepted Answer

Hello John

Thank you for reaching us. I understand that you will like to know of a possible way via which you can make use of the following expression in DMS transformation rules:

"expression": "json_object('id', $id, 'name', $name, 'next_column', $next_column)"

And I understand that the fields are dynamic in nature and there isn't a way to know the names beforehand. I did check more on these and unfortunately as you have correctly pointed out, at this point there is no mechanism to loop through the row contents to grab the field names.

If you have an active AWS Support plan I will highly encourage you to reach out to our Premium Support Team along with the use case and they will be more than happy to submit a feature request on your behalf.

DMS - Is it possible to write an expression to get a row as JSON?

Relevant content