Help with making multiple JSON objects from found records

Questions
1

JSON seems to be an amazing tool that for some reason just will not click in my brain. I can make an object no problem and I can hard code multiple objects no problem. But I can not seem to figure out how to loop though multiple records and make an object out of each record.

I have gotten it to work with an array but that seems to add a layer of complication I just do not need for my current application.

I made a test database using ice-cream flavors as a sample. I have never uploaded a file so we will see how that works.
JSON Test.fmp12 (296 KB)

Below is a sample of what I would like to get but I only got this by hard coding the second object.

{
	"Chocolate" : 
	{
		"Brand" : "AMPLE Hills",
		"Cost" : "4.75",
		"FinanceCode" : "CH",
		"NickName" : "Chocolate"
	},
	"Mint Chocolate Chip" : 
	{
		"Brand" : "Tillamook",
		"Cost" : "9.45",
		"FinanceCode" : "MCC",
		"NickName" : "Mint"
	}
}
2

Would still like to see if others have suggestion on how best to do this but I found a solution that works and at the same time I learned that WHILE can be used to go though records which is maybe even cooler.

My source was a video found here.

While ( [ 
//initial Varables
	FoundCount 	= Get(FoundCount)
	;LoopCount	= 1
	;Json 		= "{}"
	;Path		= ""



	] ; 
//condition
	LoopCount	≤ FoundCount



; [ 
	Path  = GetNthRecord ( IceCream::Name ; LoopCount )
	;Json = JSONSetElement ( JSON
		;[ Path & ".Brand"; GetNthRecord(IceCream::Brand; LoopCount  ); JSONString] 
		;[ Path & ".Cost"; GetNthRecord(IceCream::Cost; LoopCount  ); JSONString] 
		;[ Path & ".FinanceCode"; GetNthRecord(IceCream::FinanceCode; LoopCount  ); JSONString] 
		;[ Path & ".NickName"; GetNthRecord(IceCream::NickName; LoopCount  ); JSONString] 
		)
	;LoopCount = LoopCount +1
] ; 
//result 
	JSONFormatElements ( JSON )
)
3

JSON Test.fmp12.zip (90.4 KB)

This version of your file contains a new script which walks the record set building JSON as it goes.

4

Thanks for the example.

Is there an advantage to using an array for this type of output? In the solution I posted if you know the ice cream name I can get brand, cost, etc. But with your example I would also need to know the order they are in right?

As much as I like the idea of JSON I am also really confused why I am having such a hard time. I have been messing with how to setup a JSONGetElement to say pull out the brand name and for the life of me nothing that seemed to have worked before is working now. Can you give an example of how to pull out the Brand from the current found record?

5

The code that generates the JSON objects in that way was yours. It generated separate objects because you used an empty string as a target for the JSONSetElement output.

If you want to combine all the records into a single object you can do that by using a variable as the target for the JSONSetElement output. That allows you to use the name as the key in the key/value pair. Each time through the loop, a new key/value would be entered in the object stored by the variable.

As for getting data out of the JSON. If you approach the problem as someone who knows everything about the data, then it is easiest to use the names as keys and then to say JSONGetElement ( json ; "Mint Chocolate Chip.Brand" ).

However, when you don't know anything about the current data set, you will not know in advance that the key is "Mint Chocolate Chip." In that case, you have to iterate over the set of data by getting the keys, then looping through them. In this situation, there is little or no disadvantage in having an array of objects because the effort required and methods used are almost identical.

/*
field "JSON" contains an object, that contains key/value pairs
{
	"Mint Chocolate Chip" : 
	{
		"Brand" : "Tillamook",
		"Cost" : "9.45",
		"FinanceCode" : "MCC",
		"NickName" : "Mint"
	},
	"Strawberry" : 
	{
		"Brand" : "Tillamook",
		"Cost" : "9.45",
		"FinanceCode" : "Stra",
		"NickName" : "Strawberry"
	}

}
*/
Let ( json = IceCream::JSON ; JSONGetElement ( json ; "Mint Chocolate Chip.Brand" ) )


// Output is "Tillamook"

/*
field "JSON" contains an array of objects, the objects contain key/value pairs
[ 
{
	"Mint Chocolate Chip" : 
	{
		"Brand" : "Tillamook",
		"Cost" : "9.45",
		"FinanceCode" : "MCC",
		"NickName" : "Mint"
	}
},
{
	"Strawberry" : 
	{
		"Brand" : "Tillamook",
		"Cost" : "9.45",
		"FinanceCode" : "Stra",
		"NickName" : "Strawberry"
	}
} 
]
*/
Let ( json = IceCream::JSON ; JSONGetElement ( json ; "[0].Mint Chocolate Chip.Brand" ) )


// Output is "Tillamook"
1 Like
6

Since the age of 5, I have had little doubt that the key is Mint Chocolate Chip. :grinning:

2 Likes