How to load multiple json files into a single table?

[iiyoob]

I have a number of identically structured json files.  I'd like to load them into a single table such that each file is a row in the table.  The table column names should be derived from the 'key' of the json files.

For a single file, I am able to use a simple crosstab and it works.  However, when I try for multiple files using a Directory + Dynamic Input, followed by CrossTab, then only 1 row is created with comma separated values.  What I need is for these to be in separate rows, not concatenated into a single row.

Any help is appreciated.

[SeanAdams]

Hey @iiyoob,

Firstly, welcome to the community and congratulations on your first post.

Are you able to attach a simplified version of your workflow, and 2 or 3 of your JSON files to this thread, along with a quick table that shows the outcome you are looking for?   That would really help to get you to a good solution as quick as possible (very similar to the way that the weekly challenge team do this).   

Hope you have a good Sunday.

Sean

[iiyoob]

Hi @SeanAdams,

Below are two sample json files.  

auto1.json

{
  "AutomobileType": "tesla3",
  "LaunchIndex": 0,
  "ProductCodes": [],
  "PurchaseTime": "2017-02-20 23:35:27+00:00",
  "NetworkInterfaces": [
    {
      "Attachment": {
        "AttachTime": "2017-02-20 23:35:27+00:00",
        "AttachmentId": "eni-attach-3u8fns94",
      },
      "NetworkInterfaceId": "eni-4n4c9emf",
      "Status": "in-use",
    }
  ]
}

auto2.json

{
  "AutomobileType": "teslaS",
  "LaunchIndex": 0,
  "ProductCodes": [],
  "PurchaseTime": "2017-02-21 21:35:27+00:00",
  "NetworkInterfaces": [
    {
      "Attachment": {
        "AttachTime": "2017-02-20 23:35:27+00:00",
        "AttachmentId": "eni-attach-j4j2b4j4",
      },
      "NetworkInterfaceId": "eni-9dj38m33",
      "Status": "in-use",
    }
  ]
}

I am able to get what I want using the 'jsonFileJoin.yxmd' workflow, but I need to be able to get the same output using the 'jsonFolderJoin.yxmd' workflow because I have thousands of such json files.

Thanks

-Ilyas

[SophiaF · Accepted solution]

In order to get each JSON file on each row, you need to set a field to GroupBy in the Crosstab - ideally this would be some type of File_Id field. Since you can't pass any records through the Dynamic Input (other than the actual data that comes in), there are 2 ways you could do this:

1. Use a multi-row formula tool after the Dynamic Input to create a field that groups the appropriate records together by File

2. Use a batch macro to read in each file

I personally think the Batch Macro approach is easier (and less error-prone), as you can use the constant [Engine.IterationNumber] to easily make a File_ID field (this is done with the following expression in the Formula tool:

1+[Engine.IterationNumber]

You can then insert this macro instead of the Dynamic Input, and use the newly created FileID field as your Groupby field in the Crosstab

Sample workflow attached

[iiyoob]

That works @SophiaF !  I hadn't used batch macros before, so that's another thing I've learnt.  Thanks!