Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r089

...

  • Recommended limit of 1 GB in source file size. Since conversion happens within the 
    D s node
    , this limit may vary depending on the memory of the 
    D s node
    .
  • Each JSON record must be less than 1MB in size.
  • Filename extensions must be .json or .JSON
  • Conversion of compressed JSON files is not supported. Compressed JSON files can be imported using the previous method. See Working with JSON v1.
  • For best results, all keys and values should be quoted and imported as strings. 

    Info

    NOTE: Escape characters that make JSON invalid can cause your JSON file to fail to import.

    • You can escape quote values to treat them as literals in your strings using the backslash character. For example: \"

    • When the values are imported into the Transformer page, the 
      D s webapp
       re-infers the data type for each column.

...

  1. In the Recipe panel, click New Step
  2. In the Search panel, enter unnest values into new columns
  3. Specify the following transformation. Substitute the Paths to elements values below with the top-level keys in your JSON records:

    D trans
    Typeref
    p01NameColumn
    p01Valuemetrics
    p02NamePath to elements1
    p02Value[0]
    SearchTermUnnest values into new columns

    Tip

    Tip: You can choose to remove the original from the source or not. In deeper or wider JSON files, removing can help to identify what remains to be unnested.

  4. In the above transformation, the bracketing array around the set of values has been broken down into raw JSON. This value may now be interpreted as a String data type. From the column drop-down, you can select Object data type.
  5. Click the column head again, or specify the following transformation to unnest the Object column:
    D trans
    p03Valuescore
    Typeref
    p01NameColumn
    p01Value0
    p02NamePath to elements1
    p02Valuerank
    p03NamePath to elements2
    SearchTermUnnest Objects into columns

    1. In the above, each Paths to elements entry specifies a key in the JSON record. The key's associated value becomes the value in the new column, which is given the same name as the key. 
    2. So, this step breaks out the key-value pairs for the specified keys into separate columns in the dataset. 

  6. Repeat the above process for the next level in the hierarchy. 
  7. You can now delete the source columns. In the example, these source columns are named metrics and 0

    Tip

    Tip: SHIFT + click these columns and then select Delete columns from the right panel. Click Add.

  8. Repeat the above steps for each nested JSON object.

    Tip

    Tip: If the above set of steps needs to be applied to multiple files, you might consider stopping your work and returning to Flow View. Select this recipe and click Add New Recipe. If you add successive steps in another recipe, the first one can be used for doing initial processing of your JSON files, separate from any wrangling that you may do for individual files.

    Tip

    Tip: The unnesting process may have moved some columns into positions that are different from their order in the original JSON. Use the Move command from the column menu to reposition your columns.

...

  1. SHIFT + click the url and filename columns. Then, select Nest columns in the right-hand panel. This transformation should look like the following:

    D trans
    p03ValueObject
    Typeref
    p01Namecolumn1
    p01Valueurl
    p02Namecolumn2
    p02Valuefilename
    p03NameNest columns to
    p04Valuecolumn1
    p04NameNew column name
    SearchTermNest columns into Objects
  2. column1 now contains an Object mapping of the two columns. You can now nest this column again into an Array:
    D trans
    p03Valueresources
    Typeref
    p01NameColumns
    p01Valuecolumn1
    p02NameNest columns to
    p02ValueArray
    p03NameNew column name
    SearchTermNest columns into Objects
  3. Delete column1.
  4. Continue nesting other columns in a similar fashion. Repeat the above steps for the next level of the hierarchy in your dataset.
  5. You must re-nested from the bottom of the target hierarchy to the top. 

    Info

    NOTE: Do not nest the columns at the top level of the hierarchy.

  6. When the column names contain all of the keys that you wish to generate in the top-level JSON output, you can run the job.

...