Question 380603 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json file from SQL Table using ADF is adding Escape characters

I ma trying to create json file from SQL table using ADF through Stored Procedure

Stored Procedue:

SELECT (
SELECT *
FROM [dbo].[TEST_TABLE] FOR JSON PATH, INCLUDE_NULL_VALUES
) AS json_file;

I am then trying copy activity where Source is SQL Table and Sink is ADLS json but the o/p is coming as

"json_file":"[{"FIL_PATH":"/mnt/test//test_table/jsonfiles","ACTV_IND":"Y","CREATED_DATE":"2024-04-17T20:56:57.870","UPDATED_DATE":"2024-04-17T20:56:57.870","VB_OCCURS":"N"}]"}

I have eevn tried creating as a comma deleimited file instead of json as sink with the below but still it doesn’t help

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks Pratik. It worked for the most part except the below. Table data : "/mnt/test/test_table/jsonfiles" Was previously coming as : "/mnt/test//test_table/jsonfiles" but now coming as: "/mnt/test/test_table/jsonfiles" enter image description here


  2. 0

    Not to get added escape characters in Json file you need to sore the data in Json file with delimited file dataset.

    My sample stored procedure Output looks like:

    enter image description here

    To store this into Json file without escape characters you need to use delimited file dataset with below settings:

    • Add column delimiter is the character which is not present in your data.
    • Add quote character and escape character as No quote character and No escape character respectively.

    enter image description here

    Output:

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search