none
Converting Avro file to JSONs using JSONOutputer

    Question

  • I have a requirement where I need to extract JSON files from Avro by using U-SQL. Avro files contain a collection of JSON and need to extract individual JSON from Avro. Here I have written code. I used JsonOuptter() for writing <g class="gr_ gr_6 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="6" id="6">jsons</g>.

    OUTPUT @source
    TO "C:/schemaType.json"
    USING new JsonOutputter();

    I am getting JSON as like below.

    [
      {
        "type": "CE",
        "cnn": "11",
        "source": "{\r\n  \"vendorCd\": \"G\",\r\n  \"brandCd\": \"4\",\r\n  \"channelCd\": \"C\",\r\n  \"accessTypeCd\": \"I\"\r\n}",
         },
      {
        "type": "CE",
        "cnn": "9",
        "source": "{\r\n  \"vendorCd\": \"G\",\r\n  \"brandCd\": \"4\",\r\n  \"channelCd\": \"C\",\r\n  \"accessTypeCd\": \"I\"\r\n}",
          }
    ]

    I am expecting JSON as like below.

    [
    {  
       "type":"CE",
       "ccn":"1",
       "source":{  
          "vendorCd":"G",
          "brandCd":"4",
          "channelCd":"C",
          "accessTypeCd":"I"
       },
    {  
       "type":"C",
       "ccn":"9",
       "source":{  
          "vendorCd":"G",
          "brandCd":"4",
          "channelCd":"C",
          "accessTypeCd":"I"
       }
    ]

    Thanks for your help and support.



    Tuesday, May 29, 2018 3:09 PM

All replies

  • This is as expected. The JSON Outputter can only output flat information since the source column is of type string. You would probably need to use a SqlMap<string,string> typed column and extend the sample outputter to output a map as a nested structure (I am not sure if it does that yet). 

    Michael Rys

    Tuesday, May 29, 2018 6:07 PM
    Moderator
  • Thanks for your quick reply,

    I have no problem to be in single line. The issue is here is that, how can I replace or get rid of unwanted characters (\r\n \).

    Thanks

    Lalith

    Tuesday, May 29, 2018 6:21 PM
  • Kiran,

       Since it is formatted as a string, you are getting escape characters \r\n \. As @MRys explained, if you try to retrieve it as JSON instead of string. You will be able to overcome this issue.



    Monday, June 4, 2018 9:04 PM
    Moderator