Saving a table with empty numerical values as JSON results in invalid JSON primitive?

[#1]

If a save a table with empty values in one of the numerical columns like this:

New Table( "testEmpty",
	Add Rows( 2 ),
	New Column( "Col1",
		Character,
		"Nominal",
		Set Selected,
		Set Values( {"test", "test"} )
	),
	New Column( "Col2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., .] )
	)
)

as a JSON file, it will result in this:

[
 {
  "Col1" : "test",
  "Col2" : .
 },
 {
  "Col1" : "test",
  "Col2" : .
 }
]

Looks like not everybody agrees that this is a valid JSON syntax. If I read this file back to JSON using, let's say, Powershell:

$json = Get-Content -Path 'C:\Users\myUser\testEmpty.json' -ErrorAction Stop | ConvertFrom-Json

it will error out:

ConvertFrom-Json : Invalid JSON primitive: ..
At C:\Users\myUser\testEmpty.ps1:1 char:118
+ ... \myUser\testEmpty.json' -ErrorAction Stop | ConvertFrom-Json
+                                                          ~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [ConvertFrom-Json], ArgumentException
    + FullyQualifiedErrorId : System.ArgumentException,Microsoft.PowerShell.Commands.ConvertFromJsonCommand
 

Any idea how to fix this? Any options when saving JSON files using scripts? Or the only workaround is to make sure the table doesn't have empty values?

[#2 · Accepted solution]

I'm quite sure those would have to be changed into null values to be valid JSON.

If you have only two columns you could try converting datatable to associative array and that to JSON (NOTE: Associative Array won't allow duplicate keys. Valid JSON does but not sure how nice it would be to work with duplicated keys)

Names Default To Here(1);
dt = New Table( "testEmpty",
	Add Rows( 2 ),
	New Column( "Col1",
		Character,
		"Nominal",
		Set Selected,
		Set Values( {"test", "test1"} )
	),
	New Column( "Col2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., .] )
	)
);

aa = Associative Array(Column(dt, "Col1"), Column(dt, "Col2"));

json = As JSON Expr(aa);

This would look like this:

"{\!"test\!":null,\!"test1\!":null}"

and if you save it with Save Text File("c:\temp\test.json", json) it will remove those JMP escaped quotes {"test":null,"test1":null}

[#3]

Good to know for cases when I indeed have two columns, in my particular case I have many columns though.
I just changed the column to Character and if there is no number, I put "NA", and deal with that outside of JSL later.

[#4]

Just changing it to character and leaving "empty" value there will most likely work as I would guess it will be replaced with "" and that is valid for JSON.