convert json file without using Copy Activity (Azure Data Factory)

japanese(日本語)

 

Azure Data Factory.

How to convert json file format without using Copy Activity.

Basically, You shuld use Copy Activity to convert json. But If you want to do something detailed, you can manage it by using Lookup, ForEach, SetVariable Activities.

In this case, You should manage to escape strings such liken “\n”, “\r”, “\”….

1.json file on BLOB storage

{
     "datarecords" : [
          {  "name" : "name01",  "value" : " value01" },
          {  "name" : "name02",  "value" :  "value02" }
    ]
}

2.converted json

{
     "records" : [
          { 
　　　　　　　　　　　　　   "attr" : { "rid" : "name01"},
　　　　　　　　　　　　　　　　"id" : "name01", 
                 "info" : "value01"
　　　　　　　　　　},
          { 
                "attr" : { "rid" : "name01"},
　　　　　　　　　　　　　　　"id" : "name02",
                "info" : "value02"
　　　　　　　　　　}
     ]
}

 

3. Pipeline

3.1. Pileline variables

BUFFER_V1_1   String
BUFFER_V1_2   String
BUFFER_V1_3   String

3.2. Pipeline structure

3.3. Lookup1 

  • SRC …. specify  json file

  • Uncheck “First row only”

 

3.4. ForEach1 

  • Check “Sequential”

  • Items：
    @activity('Lookup1').output.value[0].datarecords

 

3.5. Set V1 

  • Name：BUFFER_V1_1

  • Value： 
    @concat(variable('BUFFER_V1_2'), '{　"attr":{ "rid": "', ESCAPE(item().name), '"},　"id":"', ESCAPE(item().name), '","info":"', ESCAPE(item().value),'"}')

ESCAPE() is omitted. you shuld write below code.

uriComponentToString(replace(replace(replace(replace(replace(urlComponent(coalesce( *** , ”)),’%5C’,’%5C%5C’), ‘%22′,’%5C%22’), ‘%0D’,’%5Cr’), ‘%0A’, ‘%5Cn’), ‘%09’, ‘%5Ct’))

replace(**, ‘%5C’,’%5C%5C) …..  replace “\” to “\\”

replace(**, ‘%22’,’%5C%22) ….. replace “”” to “\””

replace(‘**’, ‘%0D’,’%5Cr’) ….  replace \r to “\r” 

replace(‘**’, ‘%0A’,’%5Cn’) …. replace \n to  “\n”

replace(‘**’, ‘%09′,’%5Ct’) ….. replace \t  to “\t”

3.6. Set V2 

  • Name：BUFFER_V1_2
  • Value： 
    @concat(variable('BUFFER_V1_1'), ',')

 

3.7. Set V3 

  • Name：BUFFER_V1_3
  • Value： 
    @concat(variable('BUFFER_V1_3'), '{ "records" [', variable('BUFFER_V1_1'),'}')

 

。