import .json data in stata

[Marius Kaltenbach]

Hello everybody,

I have to get the data from a .json file in stata. I have been using the following command:

insheetjson  classes ID Name Address PostCode City District State Telephone using "C:\Users\marius.kaltenbach\Desktop\outfile_A_230102.json",col( "classes" "ID" "Name" "Address" "PostCode" "City" "District" "State" "Telephone")

Before executing the command I generates all variables as strings.

The commandline was successfully executed but only one Observation was submitted and I don't know what happened to the rest. I am no expert regarding json (and the insheetjson command), so it is possible that I made a trivial mistake somewhere.

Here is a small part of the .json File, maybe that is helpful.

Thanks!

{
    "classes": [
        {
            "students": [
                {
                    "assessments": [
                        {
                            "TestRules": [
                                {
                                    "TestID": "0",
                                    "Invoke": "0",
                                    "Complete": "1",
                                    "Skip": "0"
                                },
                                {
                                    "TestID": "1",
                                    "Invoke": "1",
                                    "Complete": "0",
                                    "Skip": "0"
                                },
                                {
                                    "TestID": "2",
                                    "Invoke": "0",
                                    "Complete": "0",
                                    "Skip": "1"
                                },
                                {
                                    "TestID": "3",
                                    "Invoke": "0",
                                    "Complete": "1",
                                    "Skip": "0"
                                },
                                {
                                    "TestID": "4",
                                    "Invoke": "0",
                                    "Complete": "1",
                                    "Skip": "0"
                                },
                                      
.........
                    },
                    "IsDAZ": null,
                    "AnonID": "99999999-30",
                    "FirstName": "",
                    "FamilyName": "",
                    "DateOfBirth": "",
                    "Gender": null
                }
            ],
            "ID": {
                "value": "30272b9e-701f-11e9-9452-5314d1816c29"
            },
            "Name": "999999",
            "Notes": ""
        }
    ],
    "ID": 0,
    "Name": "Test School",
    "Address": "",
    "PostCode": "",
    "City": "",
    "District": "",
    "State": "",
    "Telephone": ""
}

[JanDitzen]

Hi Marius,
I think I had a similar problem with insheetjson and started using jsonio instead. I tried your example, but failed and I noted that some of the squared brackets are not closed.
Best,
Jan

[Marius Kaltenbach]

Hi Jan,

thanks you for answering. I showed just a part of the .json file, so I might have cut out some brackets. In tota,l there should not be any mistake within the file. I tried also jsonio, but it returns me constantly the following errors:

java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.stata.Javacall.load(Javacall.java:130)
        at com.stata.Javacall.load(Javacall.java:90)

EDIT: download another Version of jsonio and it works fine noq, thanks!

[Marius Kaltenbach]

A small follow up question. I was able to import the data with

jsonio kv, file("C:\Users\marius.kaltenbach\Desktop\outfile_A_230102.json") nourl

which gives me two variables: key and value. The key variable has the following strings:

/classes_1/students_1/assessments_1/TestRules/id_6/Complete
/classes_1/students_1/assessments_1/TestRules/id_6/Skip
/classes_1/students_1/assessments_1/TestRules/id_7/TestID
/classes_1/students_1/assessments_1/TestRules/id_7/Invoke
/classes_1/students_1/assessments_1/TestRules/id_7/Complete

Is it possible to created two more variables next to key and value out of the json file without using

jsonio rv

[JanDitzen]

I usually split the output variable of jsonio as the following:

split key , parse("/")

This then will create 6 variables with the content in between the "/".