jsonimport(JSON) import to variables

Is there a way using jsonimport(JSON) to import to a variable or variables rather than fields?

I just noticed I already kind of asked this question and Jims answer was

No, you cannot choose to create a dictionary or data array – that choice is made depending on what the top level of the JSON data is. If the top level is a dictionary, you’ll get a dictionary, if it is an array, you’ll get a data array. Unlike some other features like importjson , the jsonimport( function puts no restrictions on what sort of JSON structure it will accept. Whatever the structure of the JSON file is, you’ll get a mirror of that in your variable. And yes, you are correct, you would use this to create a variable.

So when I use jsonimport() and assign to a variable how would I access the data arrays in the variable?

The Help page for Data arrays makes note of the various array functions that work with data arrays. They are all used without a separator parameter when dealing with data arrays.

Thank you Gary,

Specifically I am trying to work with the example

{
“Name” : “Tonya Adams”,
“Title” : “Editor”,
“Phone” :
{
“Work” : “(847) 442-9283”,
“Home” : “(847) 309-2284”,
“Cell” : “(903) 662-3022”
}
}

I am trying to take the example, convert it to a dictionary, then dump it back to json using this code where “B” = the data above.

jsonexport(dictionaryfromarray(B, “:”,“,”))

I get this result

{
“{\r "Name" " : " "Tonya Adams"”,
“\r "Cell" " : " "(903) 662-3022"\r }\r }”,
"\r "Phone" " : " \r {\r "Work" ",
“\r "Title" " : " "Editor"”,
“\r "Home" " : " "(847) 309-2284"”
}

Your example json contains a dictionary within a dictionary – there is no JSON array there. If you ran the formula

jsonimport(B)

you would get the same result as:

initializedictionary(
    "Name","Tonya Adams",
    "Title","Editor",
    "Phone",initializedictionary(
        "Work","(847) 442-9283",
        "Home","(847) 309-2284",
        "Cell","(903) 662-3022"
    )
)

The dictionaryfromarray( function expects a text array. You’ve given it JSON, it has no idea what to do with that. Well actually, it does, it is parsing it according to the separators you supplied, but those make no sense with JSON text. So the result you got makes no sense. It’s giving you exactly the results you asked for.

I’d like to make a more constructive suggestion, but unfortunately I have no idea where you are trying to go with this. Let me try this, suppose you have a database with the fields Name, Title, Work, Home and Cell. You could import this JSON into your database like this:

let person = jsonimport(B)
Name = getdictionaryvalue(person,"Name")
Title = getdictionaryvalue(person,"Title")
let phones = getdictionaryvalue(person,"Phone")
Work = getdictionaryvalue(phones,"Work")
Home = getdictionaryvalue(phones,"Home")
Cell = getdictionaryvalue(phones,"Cell")

Now suppose you have an array of similarly formatted JSON data like this:

[
{
    “Name” : “Tonya Adams”,
    “Title” : “Editor”,
    “Phone” :
        {
            “Work” : “(847) 442-9283”,
            “Home” : “(847) 309-2284”,
            “Cell” : “(903) 662-3022”
        }
},
    “Name” : “Mark Wilson”,
    “Title” : “Writer”,
    “Phone” :
        {
            “Work” : “(847) 454-1234”,
            “Home” : “(847) 342-1234”,
            “Cell” : “(903) 909-2394”
        }
},
]

I’ll assume this is in a variable named Personnel. Here’s how you could import this data into your database.

let people = jsonimport(Personnel)
for p,1,arraysize(people) // unfortunately looparray doesn't work with data arrays
    let person = array(people,p)
    addrecord
    Name = getdictionaryvalue(person,"Name")
    Title = getdictionaryvalue(person,"Title")
    let phones = getdictionaryvalue(person,"Phone")
    Work = getdictionaryvalue(phones,"Work")
    Home = getdictionaryvalue(phones,"Home")
    Cell = getdictionaryvalue(phones,"Cell")
endloop

I hope this is helpful to you Steve, or if not you, someone else trying to use JSON in the future.

How about loopdatarray?

Yes, and a pony!

Seriously, that would be nice someday, looparray is a statement I use almost daily, but the list of “would be nice someday” is years long. And it’s good that the for statement gets used every now and then – it was getting lonely!

1 Like

I use it! Old BASIC habits die hard.

Ok, one pony coming right up!

I thought I searched for loopdataarray. Lockdown makes my brain fuzzy, I guess.

Fuzzy, but at least not loopy!

1 Like

I meant to post these earlier, possibly useful for learning about JSON.

https://www.json.org/json-en.html

Maybe it’s just time for your 50,000 mile check up. I’m already well past warranty myself.