How to remove escaping backslashes in literal JSON string output

Question

I am trying to write a script that dynamically reads all the available columns of a certain table and removes/adds a certain formatting to that column based on some T-SQL functions. I am using an azure Synapse pipeline to lookup/format the available column names via a JSON script and try to copy the data into a parquet file.

When I use the output of the JSON script as input for my column mapping, the step returns an error since parquet does not accept the literal JSON array strings. I would like to remove the escape backslash in the output and pass the column names as strings. Parquet does not accept the backslashes as input for column names.

This is the script i am using as input:

    DECLARE @json_construct varchar(MAX) = ''{"type": "TabularTranslator", "mappings": {X}}'';
 DECLARE @json VARCHAR(MAX);

 SET @json = (
   SELECT
        ''source.name''  = ''['''''' + c.[name] + '''''']''
       ,''sink.name''    = LOWER(REPLACE(TRIM(REPLACE(c.[name], ''_'', '''')), '' '', ''_''))
   FROM sys.tables                 t
   JOIN sys.schemas                s ON s.schema_id        = t.schema_id
   JOIN sys.all_columns            c ON c.object_id        = t.object_id
   JOIN sys.types                  y ON c.system_type_id   = y.system_type_id
                                     AND c.user_type_id  = y.user_type_id
   WHERE   1 = 1
       AND t.[name] = ''',string(pipeline().parameters.table_name),'''
       AND s.[name] = ''',string(pipeline().parameters.schema_name),'''
   ORDER BY c.column_id
   FOR JSON PATH );

   SELECT REPLACE(@json_construct,''{X}'', @json) AS json_output;

The problem is situated in the output of the ''sink.name''. Does somebody know how to remove the backslashes in the JSON string so that parquet accepts the column names?

Thank you in advance!

user_18844585 · Answer

I tried to reproduce the issue using the query given and the following procedure allowed me to achieve the requirement.

I have a table called SalesLT.Customerin my database where SalesLT is the value of schema_name parameter and Customer is the value for table_name parameter.
I used Script activity to execute the query given with slight modifications in Pipeline Expression Builder. It generates the output as shown below:

DECLARE @json_construct varchar(MAX) = '{"type": "TabularTranslator", "mappings": {X}}';
DECLARE @json VARCHAR(MAX);

SET @json = (SELECT
'source.name' = c.[name]
,'sink.name' = LOWER(REPLACE(TRIM(REPLACE(c.[name], '_', '')), ' ', '_'))
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.all_columns c ON c.object_id = t.object_id
JOIN sys.types y ON c.system_type_id = y.system_type_id
AND c.user_type_id = y.user_type_id
WHERE 1 = 1
AND t.[name] = '@{pipeline().parameters.table_name}'
AND s.[name] = '@{pipeline().parameters.schema_name}'
ORDER BY c.column_id
FOR JSON PATH );

SELECT REPLACE(@json_construct,'{X}', @json) AS json_output;

enter image description here

The value of json_output is of String type (object as string). Now I have used the copy data activity with my source table as @pipeline().parameters.schema_name.@pipeline().parameters.table_name. For sink, I chose parquet file format.
In the mapping section, I have used the following dynamic content. Since the value of json_output has a String value, we need to convert its value into an object first using @json() and pass it to mapping. This helps to create the required copy data source code to map as per requirement.

@json(activity('Script1').output.resultSets[0].rows[0].json_output)

The following image of the copy data activity source code indicates the correct syntax for mapping to occur (taken from debug).
The pipeline would run successfully and generate the required parquet sink with modified column names (as per the result of the query).

enter image description here

I used look up activity to demonstrate the output:

enter image description here

How to remove escaping backslashes in literal JSON string output

1 Answers