How to parse a Json with array and objects and export the data into Excel file

#How to parse a Json with array and objects and export the data into Excel file?

Source: StackOverflow.com

Question

I need to parse a JSON and export the data into Excel file with all the fields in the JSON. My JSON is as follows:

{
  "id": 1255,
  "title": "The Brain and Nervous System (LS1.D)",
  "description": "By the time you finish this playlist, you should be able to: 1. Describe how the nervous system is organized and how it works 2. Describe the role of the nervous sytem, and explain how our different senses work to send information to your brain",
  "keyTerms": "You should also be able to define the following words: stimulus, response, neuron, sensory neuron, motor neuron, nerve impulse, dendrite, axon, nerve, central nervous system, peripheral nervous system, brain, spinal cord, cerebrum, cerebellum, brain stem, retina, cochlea",
  "visible": true,
  "introduction": {
    "id": 5336,
    "title": "Introductory Materials",
    "resources": [
      {
        "id": 23022,
        "title": "Vocabulary - Brain and Nervous System",
        "description": "",
        "purpose": "",
        "category": "Website",
        "position": 1,
        "contentItem": {
          "id": 1650,
          "url": "http://quizlet.com/45497180/flashcards",
          "itemType": "Website",
          "embedUrl": null
        }
      },
      {
        "id": 23023,
        "title": "The Brain and Nervous System Study Guide",
        "description": "Fill out this study guide while studying! It will help you prepare for the assessment!",
        "purpose": "",
        "category": "Website",
        "position": 2,
        "contentItem": {
          "id": 12581,
          "url": "https://docs.google.com/a/summitps.org/document/d/1TjF1MY3cyGNKT4s46uk1iz5NvjrY59eNPH8YKYYTC_E/edit",
          "itemType": "Website",
          "embedUrl": null
        }
      }
    ]
  },
  "objectives": [
    {
      "id": 10732,
      "title": "1. Describe how the nervous system is organized",
      "caContribution": 5,
      "position": 1,
      "resources": [
        {
          "id": 23024,
          "title": "Reading - How the Nervous System Works",
          "description": "",
          "purpose": "",
          "category": "Document",
          "position": 1,
          "contentItem": {
            "id": 1651,
            "url": null,
            "itemType": "Document",
            "embedUrl": "https://view-api.box.com/1/sessions/493fca96d46a4559813c3118ebeef8b6/view?theme=light",
            "s3Url": "/files/content_items/relateds/000/001/651/original/53d1ddd8f07787731aa7d84f-how_20nervous_20system_20works_001.pdf?1424368501"
          }
        }
      ]
    },
    {
      "id": 10734,
      "title": "2. Describe the role of the nervous sytem, and explain how our different senses work to send information to your brain",
      "caContribution": 5,
      "position": 2,
      "resources": [
        {
          "id": 23039,
          "title": "Study Jams - The Senses",
          "description": "This series of videos explains the different senses: sight, hearing, taste, touch, and smell",
          "purpose": "",
          "category": "Website",
          "position": 1,
          "contentItem": {
            "id": 1666,
            "url": "http://studyjams.scholastic.com/studyjams/jams/science/human-body/touching.htm",
            "itemType": "Website",
            "embedUrl": null
          }
        },
        {
          "id": 23040,
          "title": "Nervous System: I'm Sensing Something",
          "description": "Format: Article Content: How does the nervous system work and interact with other systems",
          "purpose": "",
          "category": "Website",
          "position": 1,
          "contentItem": {
            "id": 12582,
            "url": "http://www.biology4kids.com/files/systems_nervous.html",
            "itemType": "Website",
            "embedUrl": null
          }
        }
      ]
    }
  ]
}

I want the output fields like id, title, description, keyTerms, visible, introduction.id ,introduction.title ,introduction.resources.id ,introduction.resources.title ,..., objectives.id, objectives.title, objectives.description, .....,etc.

I need to parse the object and array dynamically and produce the fields in each column of the spreadsheet.

The sample output with columns and data are as follows:

id  title  description  keyTerms  visible  introduction.id  introduction.title  introduction.resources.id  introduction.resources.title  introduction.resources.description  introduction.resources.purpose  introduction.resources.category  introduction.resources.position  introduction.resources.contentItem.id  introduction.resources.contentItem.url  introduction.resources.contentItem.itemType  introduction.resources.contentItem.embedUrl  objectives.id  objectives.title  objectives.caContribution  objectives.position  objectives.resources.id  objectives.resources.title  objectives.resources.description  objectives.resources.purpose  objectives.resources.category  objectives.resources.position  objectives.resources.contentItem.id  objectives.resources.contentItem.url  objectives.resources.contentItem.itemType  objectives.resources.contentItem.embedUrl  objectives.resources.contentItem.s3Url

1255  The Brain and Nervous System (LS1.D)  By the time you finish...  You should also...  true  5336  Introductory Materials  23022  Vocabulary - Brain and Nervous System  null  null  Website  1  1650  http://quizlet.com/...  Website  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null
1255  The Brain and Nervous System (LS1.D)  By the time you finish...  You should also...  true  5336  Introductory Materials  23023  The Brain and Nervous System Study Guide  Fill out this...  null  Website  2  12581  https://docs.google.com/...  Website  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null
1255  The Brain and Nervous System (LS1.D)  By the time you finish...  You should also...  true  null  null  null  null  null  null  null  null  null  null  null  null  10732  1. Describe how...  5  1  23024  Reading - How...  null  null  Document  1  1651  null  Document  https://view-api.box.com/1...  /files/content...  
1255  The Brain and Nervous System (LS1.D)  By the time you finish...  You should also...  true  null  null  null  null  null  null  null  null  null  null  null  null  10734  2. Describe the role...  5  2  23039  Study Jams - The Senses  This series of videos  null  Website  1  1666  http://studyjams.scholastic.com...  Website  null

Answer

You can do it with AlaSQL javascript library with special SEARCH operator, which is designed to do search for nested objects. See the code below, which generates the Excel file with your structure (I did not include all fields):

var alasql = require('alasql');

var data = [{
   "id": 1255,
   "title": "The Brain and Nervous System (LS1.D)",
   // ...
},
{
   "id": 1256,
    // ...
}];

// Here is search query
alasql('SEARCH / AS @a \
    UNION ALL( \
      introduction AS @b \
      resources / AS @c \
      RETURN(@a->id AS id, @a->title AS title, @a->description AS description, \
        @a->keyTerms AS keyTerms, @a->visible AS visible, \
        @b->id as [introduction.id], @b->title as [introduction.title], \
        @c->id AS [introduction.resources.id], \
        @c->contentItem->id AS [introduction.resources.contentItem.id] \
      ) \
    , \
      objectives AS @b \
      resources / AS @c \
      RETURN(@a->id AS id, @a->title AS title, @a->description AS description, \
        @a->keyTerms AS keyTerms, @a->visible AS visible, \
        @b->id as [introduction.id], @b->title as [introduction.title], \
        @c->id AS [introduction.resources.id], \
        @c->contentItem->id AS [introduction.resources.contentItem.id] \
      ) \
    ) INTO XLSX("test411.xlsx",{headers:true})\
    FROM ?',[data]);

Some explanations:

• SEARCH - is a special statement for query nested objects
• / - loop over array elements
• UNION ALL(...,...) - union of all nested found records
• AS @variable - save current search position to temporary variable
• introduction - go deep into the property "introduction"
• RETURN(...,...) - create a record
• RETURN(value AS alias) - alias for the value
• @a->id - get id property similar to a.id in JavaScript
• [...] - terms with any special charaters
• INTO XLSX("test411.xlsx",{headers:true}) - save results to Excel file with headers
• FROM ? - get data from parameter
• alasql(sql, [data]) - put data variable as first parameter of the query

You also need to add other columns to finish this query.

See also: SEARCH