Depivot json with different number of attributes

Robi_MeRobi_Me Member Posts: 32 Maven
edited January 2021 in Help
How does one depivot a json file that has two different categories, with some shared and some unshared attributes that leads to different attribute counts? 

Does one have to split the file before de-pivoting or is there a better way of selecting the attributed for de-pivoting?

I have attached the process as well as the json file. 

<?xml version="1.0" encoding="UTF-8"?><process version="9.7.002">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.4.000" expanded="true" name="Process">
    <parameter key="logverbosity" value="init"/>
    <parameter key="random_seed" value="2001"/>
    <parameter key="send_mail" value="never"/>
    <parameter key="notification_email" value=""/>
    <parameter key="process_duration_for_mail" value="30"/>
    <parameter key="encoding" value="SYSTEM"/>
    <process expanded="true">
      <operator activated="true" class="text:read_document" compatibility="9.3.001" expanded="true" height="68" name="Read Document" width="90" x="112" y="34">
        <parameter key="file" value="/Users/robinmeisel/Dropbox/Ornico/Instagram/json/radioawards.json"/>
        <parameter key="extract_text_only" value="true"/>
        <parameter key="use_file_extension_as_type" value="true"/>
        <parameter key="content_type" value="txt"/>
        <parameter key="encoding" value="SYSTEM"/>
      </operator>
      <operator activated="true" class="text:json_to_data" compatibility="9.3.001" expanded="true" height="82" name="JSON To Data" width="90" x="246" y="34">
        <parameter key="ignore_arrays" value="false"/>
        <parameter key="limit_attributes" value="false"/>
        <parameter key="skip_invalid_documents" value="false"/>
        <parameter key="guess_data_types" value="true"/>
        <parameter key="keep_missing_attributes" value="false"/>
        <parameter key="missing_values_aliases" value=", null, NaN, missing"/>
      </operator>
      <operator activated="true" class="de_pivot" compatibility="9.7.002" expanded="true" height="82" name="De-Pivot (5)" width="90" x="380" y="34">
        <list key="attribute_name">
          <parameter key="comment" value=".*edge_media_to_comment.*"/>
          <parameter key="text" value=".*edge_media_to_caption.*"/>
          <parameter key="liked" value=".*edge_liked_by.*"/>
          <parameter key="name" value=".*node.owner.username.*"/>
          <parameter key="url" value=".*node.display_url.*"/>
        </list>
        <parameter key="index_attribute" value="id"/>
        <parameter key="create_nominal_index" value="false"/>
        <parameter key="keep_missings" value="false"/>
      </operator>
      <connect from_op="Read Document" from_port="output" to_op="JSON To Data" to_port="documents 1"/>
      <connect from_op="JSON To Data" from_port="example set" to_op="De-Pivot (5)" to_port="example set input"/>
      <connect from_op="De-Pivot (5)" from_port="example set output" to_port="result 1"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
    </process>
  </operator>
</process>

Best Answer

  • frederic_gomer_frederic_gomer_ Member Posts: 4 Contributor I
    Solution Accepted
    @Robi_Me, try this process in dev environnment, the second output and third output of the SUPER JSON to DATA show the XML parameters for depivot and the first output try to automate the depivot process (it's working for a little nested json but it's not working for big nested json)

Answers

  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn
    Hi @Robi_Me,

    this looks like a use case for the JQ extension.

    Regards,
    Balázs
  • Robi_MeRobi_Me Member Posts: 32 Maven
    @BalazsBarany this looks very promising, the only problem is that it comes up with a security violation on the free version of Studio. 
    I have set the the enhanced permissions in Start Up,, any idea what could be causing this issue.?

  • Robi_MeRobi_Me Member Posts: 32 Maven
    @frederic_gomer_ you went above and beyond sir! Thank you!
  • Robi_MeRobi_Me Member Posts: 32 Maven
    If anyone is looking to depivot Instagram look here #instagram 
Sign In or Register to comment.