Depivot json with different number of attributes

Robi_Me

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>

frederic_gomer_

@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)

BalazsBaranyRM

Hi @Robi_Me,

this looks like a use case for the JQ extension.

Regards,
Balázs

Robi_Me

@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.?

frederic_gomer_

@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)

Robi_Me

@frederic_gomer_ you went above and beyond sir! Thank you!

Robi_Me

If anyone is looking to depivot Instagram look here #instagram