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.