Select Specified JSON Nodes to Store in a CSV File

 

Problem description & analysis

Below is the content of JSON file json.json:

{

    "issues":[

        {

            "key":"abc",

            "fields":{

                "issuetype":{

                    "name":"Bug",

                    "id":"1",

                    "subtask":false

                },

                "priority":{

                    "name":"Major",

                    "id":"3"

                },

                "created":"2020-5-11",

                "status":{

                    "name":"OPEN"

                }

            }

        },

        {

            "key":"def",

            "fields":{

                "issuetype":{

                    "name":"Info",

                    "id":"5",

                    "subtask":false

                },

                "priority":{

                    "name":"Minor",

                    "id":"2"

                },

                "created":"2020-5-8",

                "status":{

                    "name":"DONE"

                }

            }

        }

    ]

}

We are trying to retrieve certain nodes in different levels of the JSON file to generate a CSV file, as shown below:

abc,Bug,Major,2020-5-11,OPEN

def,Info,Minor,2020-5-8,DONE

Solution

Write the following script p1.dfx in esProc:

A

1

=json(file("json.json").read()).issues.new(key,fields.issuetype.name,fields.priority.name,fields.created,fields.status.name)

2

=file("result.csv").export@c(A1)

Explanation:

A1   Read the JSON file as a string, parse it as a multilevel table sequence using json() function, and get desired nodes.

A2  Export retrieved nodes to result.csv.

Q & A Collection

https://stackoverflow.com/questions/61753408/how-to-select-fields-in-different-levels-of-a-jsonfile-with-jsonpath