How to parse different part of json file (Newbie here)

I have created a python code that extracts jitter, latency, link, packet loss, timestamp from json file to csv file. However, inside this json file, there are multiple tests done (e.g. bronek 1, bronek 2, bronek 3, etc.) that under these testings have pair keys of jitter, latency, link, packet loss and timestamp.

I have pasted here a sample content of the json file and the python code.

How It can be extract these multiple testing from json file to csv with corresponding jitter, latency, link, packet loss and timestamp data?

python code:

import json
import csv

with open("/Users/olicaluag/Desktop/capture_for_Oliver.txt") as file:
data = json.load(file)

fname = “oli.csv”

with open(fname, “w”) as file:
csv_file = csv.writer(file)
csv_file.writerow([“Jitter”, “Latency”, “Link”, “Packetloss”, “Timestamp”])
for result in data[“result”]:
for data_item in result[“data”]:
for result in data_item[“response”][“results”]:
for item in result[“logs”]:
csv_file.writerow([item[“jitter”], item[“latency”], item[“link”], item[“packetloss”], item[“timestamp”]])

json file content:

“name”: “bronek 1”
},
{
“interface”: “port1”,
“logs”: [
{
“jitter”: 6.111866,
“latency”: 5.907633,
“link”: “up”,
“packetloss”: 0,
“timestamp”: 1624284672
},
{
“jitter”: 6.111166,
“latency”: 5.908467,
“link”: “up”,
“packetloss”: 0,
“timestamp”: 1624284672
},
“name”: “bronek 2”
},
{
“interface”: “port1”,
“logs”: [
{
“jitter”: 6.111866,
“latency”: 5.907633,
“link”: “up”,
“packetloss”: 0,
“timestamp”: 1624284672
},
{
“jitter”: 6.111166,
“latency”: 5.908467,
“link”: “up”,
“packetloss”: 0,
“timestamp”: 1624284672
},
“name”: “bronek 3”
},
{
“interface”: “port1”,
“logs”: [
{
“jitter”: 6.111866,
“latency”: 5.907633,
“link”: “up”,
“packetloss”: 0,
“timestamp”: 1624284672
},
{
“jitter”: 6.111166,
“latency”: 5.908467,
“link”: “up”,
“packetloss”: 0,
“timestamp”: 1624284672
},

  1. json.loads is usually used for loading text files.

  2. your json content looks seriously misformatted. There is no way this stuff could be converted into a Python dict, which is what json would try to do. If these are the actual file contents, json should have raised a JSONDecodeError. The file must match the grammar specs at json.org

Thanks Milton and you are correct that is not the correct content of the json file as I have mentioned in my statements. What I am trying to share here is that the json file have different tests done (e.g bronek1, 2, 3) so I need to extract those information under it (e.g. jitter, packet loss, etc.) in a csv file.

Can you get it to be a valid JSON file? At that point interpreting the data would probably be easier.

As it is now, you’d either need to be told the specification of that file, or you’d have to guess at it. That’s annoying and error-prone.

how to attach file here?

here is the json file. How to parse key pair data under bronek’s name?

{
“id”: 1,
“result”: [
{
“data”: [
{
“response”: {
“build”: 1923,
“http_method”: “GET”,
“name”: “sla-log”,
“path”: “virtual-wan234”,
“results”: [
{
“interface”: “port1”,
“logs”: ,
“name”: “Default_DNS”
},
{
“interface”: “port1”,
“logs”: ,
“name”: “Default_Office”
},
{
“interface”: “port1”,
“logs”: ,
“name”: “Default_Gmail”
},
{
“interface”: “port1”,
“logs”: ,
“name”: “Default_AWS”
},
{
“interface”: “port1”,
“logs”: ,
“name”: “Default_Google Search”
},
{
“interface”: “port1”,
“logs”: ,
“name”: “Default_FortiGuard”
},
{
“interface”: “port1”,
“logs”: [
{
“jitter”: 0.053500,
“latency”: 15.368967,
“link”: “up”,
“packetloss”: 0,
“timestamp”: 1624284917
},
{
“jitter”: 0.053400,
“latency”: 15.368033,
“link”: “up”,
“packetloss”: 0,
“timestamp”: 1624284917
},
{
“jitter”: 0.054933,
“latency”: 15.364767,
“link”: “up”,
“packetloss”: 0,
“timestamp”: 1624284918
},
{
“jitter”: 0.045967,
“latency”: 15.268300,
“link”: “up”,
“packetloss”: 0,
“timestamp”: 1624285049
},

                            ],
                            "name": "bronek2"
                        },
                        {
                            "interface": "port1",
                            "logs": [
                                {
                                    "jitter": 1.087900,
                                    "latency": 17.333067,
                                    "link": "up",
                                    "packetloss": 0,
                                    "timestamp": 1624285271
                                },
                                {
                                    "jitter": 1.091900,
                                    "latency": 17.317566,
                                    "link": "up",
                                    "packetloss": 0,
                                    "timestamp": 1624285271
                                },
                                {
                                    "jitter": 1.080300,
                                    "latency": 17.334734,
                                    "link": "up",
                                    "packetloss": 0,
                                    "timestamp": 1624285272
                                },
                                {
                                    "jitter": 1.767367,
                                    "latency": 18.038967,
                                    "link": "up",
                                    "packetloss": 0,
                                    "timestamp": 1624285272
                                }
                            ],
                            "name": "bronek3"
                        }
                    ],
                    "serial": "FOSVM1LTLDX1NXSS03",
                    "status": "success",
                    "vdom": "root",
                    "version": "v7.3.2"
                },
                "status": {
                    "code": 0,
                    "message": "OK"
                },
                "target": "FortiOS-VM845"
            }
        ],
        "status": {
            "code": 0,
            "message": "OK"
        },
        "url": "sys/proxy/json"
    }
]

}

You should study some tutorials on Python dicts to solve this problem for yourself.

Your file appears to legal JSON mostly. I did notice one odd thing:
The file contains several lines like: “logs”: .

My Chromium html inspector says the ‘’ is actually an html element of class ‘chklst-box’, not ‘[’ and ‘]’, which would indicate an empty list.

If this is not an artifact of pasting the code into this forums web page, it may be an artifact of copying the data out of some web page. I think you should get a JSONDecodeErrror on trying to load the file

I cleaned up your JSON a bit and then pasted it into the box in the left in Instantly parse JSON in any language | quicktype and got some Python types that might help. Apparently quicktype does not use dictionaries for the deserialized JSON.

quicktype apparently allows the JSON to be used in a more native (as in JavaScript) manner.

I assume you still want a python solution.

If you remove all the key : value lines where the value is not a dict or list, the structure of the file becomes more clear.

{
	“id”: 1,
	“result”: [
		{
		“data”: [
			{
			“response”: {
			“results”: [
				{
				},
				...
				{
				},
				{
				“interface”: “port1”,
				“logs”: [
					{
					},
					...
					{
					},
				],
            "name": "bronek2"
            },

You could access ‘bronek_2’ like:

d = json.loads(filename)
bronek_2_data = d[‘result’][0]
bronek_2_name = d[‘result’][0][‘name’]

Thus the bronek_2 data is the first list element in d[‘result’][‘data’]

Converting the data to csv form is another problem. Your best approach will be to extract the values you want for each test, put them in a list and convert the list to a string for write to a csv file.

There is currently one or more posts here on that subject, you could look at them for clues

quck question, my python code already can extract jitter, latency, link, packetloss, timestamp.
Any idea, what needs to add to my code that the information belongs to, say, Bronek2 or Bronek3. I am a newbie and need your kind assistance. Also, do i need to if/else statement in my code for decision in parsing information belongs to each test (e.g. Bronek2 or 3)?

python code:

import json
import csv

with open("/Users/olicaluag/Desktop/capture_for_Oliver.txt") as file:
data = json.load(file)

fname = “oli.csv”

with open(fname, “w”) as file:
csv_file = csv.writer(file)
csv_file.writerow([“Jitter”, “Latency”, “Link”, “Packetloss”, “Timestamp”])
for result in data[“result”]:
for data_item in result[“data”]:
for result in data_item[“response”][“results”]:
for item in result[“logs”]:
csv_file.writerow([item[“jitter”], item[“latency”], item[“link”], item[“packetloss”], item[“timestamp”]])

d = json.loads(filename)
bronek_2_data = d[‘result’][0]
bronek_2_name = d[‘result’][0][‘name’]

Thus the bronek_2 data is the first list element in d[‘result’][‘data’]

sounds good to me, i will try this in my code

If anyone got the impression that quicktype is not Python then please let me say it is a Python solution. It is likely that quicktype would make the Python code (other than the automatically generated code) easier.

In JavaScript, JSON is objects, not dictionaries. Perhaps I caused automatic confusion by mentioning .Net (C#); I have removed that part. I should have said that quicktype apparently allows the JSON to be used in a more native (as in JavaScript) manner.

I think that you will have a problem using the name ‘result’ as a loop variable in both the outer and inner loops. You should use a different name for the inner loop.

Before the line ‘for item in result[“logs”]:’ you can say:
name = result[‘name’]

Then if you only want bronek2 logs:

if name == ‘bronek2’:

And finally:
csv.writerow([name,item[‘latency’], etc.])

I noticed in another post that the poster who used csv.writerow on a list got a file line like:
‘[ value1, value2, …]’

The brackets would cause problems for programs like Excel, and there was also a lot of whitespace between the number values. If you format the numbers yourself using string format(), you can control the appearance of the file lines to suit yourself, with something like:

jitter_s = ‘{:+f}’.format(item[‘jitter’])

Then:
s = ‘,’.join(name, jitter_s, …)

Finally just write s to the file:

Hi Milton, thank you for sharing your thoughts, looking at your recommendation above, correct me if Im wrong, that with this command:
csv.writerow([name,item[‘latency’], etc.]) it will include the name (e.g. Bronek2) once the code passes to the content of json file? I am not only up to Bronek2’s test results, I need also to capture every user’s test that will be included in the json file in the future.

Correct me again, that in my code, I dont really need below line command, for I dont need to test whether its Bronek2. As mentioned, I need to parse every test done by the user(e.g. Bronek) and write it on csv file.
I havent tried yet, adding your suggested line command, but again, Thank You for sharing it. i will try it and let you know of the results.

if name == ‘bronek2’: