356068_en-US

liliconcom

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

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?

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

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}