Question 104272 in Azure Question posted in
The official documentation to get you started can be found here.

Split Json array into table rows using azure data factory

I’m using Azure Data Factory and I have Json files in azure Datalake. Each Json file contain an array of Jsons. I want to copy each Jsons of the array into Azure SQL row. I tried to do it with copy activity, but it automatically flatten the Json and I want to keep it original. I cannot use DataFlow.
The following pic describe what I want to achieve (don’t mention the values of the table)

enter image description here

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I figured it out, I used Lookup activity with Json linked service. then, with script activity, I wrote each @item() to the azure sql table row enter image description here


  2. 0

    • In order to copy each Json array into a single column of a row, you can
      use openjson in script activity. Below is the approach.

    • Lookup activity is taken, and Json file is taken as the dataset in the activity.

    enter image description here

    • Then the output of the lookup activity is stored in the variable Json
      of string type. The value is assigned using set variable activity.
      Value for variable Json: @string(activity('Lookup1').output.value)
      enter image description here

    • Script activity is taken and linked service for Azure SQL database is given. Script is given as

    declare @json nvarchar(4000)=N'@{variables('Json')}';
INSERT INTO test_tgt
SELECT * FROM OPENJSON(@json)
WITH (
col1 nvarchar(max) '$' AS JSON
);

    This script will insert the data into the table test_tgt which is already created.

    enter image description here

    SQL table output

    enter image description here

    Login or Signup to reply.
  3. 0

    I think you have to use dynamic expressions here.
    Try to parse the output of your select activity ( the one that fetch the json file from your datalake).

    Here is an exemple that would extract two seperate json variables from an array of json on a datalake.

    my pipeline

    the json file is like :

    my json file

    lookup activity would be as below :

    lookup activity

    variables are :

    variabes

    output of the lookup activity is :

    output of lookup activity

    To parse the array of json (output of the lookup activity) we use the following :

    @array(activity('Lookup1').output.value[0])

NOTE : [number] would give the item number of the array
[0] : first item
[1] scond item
..

    setvariable

    Results :

    variable 1 gets :

    @array(activity('Lookup1').output.value[0])

    var 1

    variable 2 gets:

    @array(activity('Lookup1').output.value[1])

    var 2

    Hope this would help.

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

Back To Top
Search