JsonExport and JsonExportLine

Panorama X Beta 10.2
1

I’m using ExportJson to export some records to a json file.

I’m running into an issue that not all fields are being emitted to the json file and it appears that ExportJsonLIne may be completely ignoring blank fields within a record.

For example, if I have these records in a db:

name | middle | last  | DOB
Mark |        | Jones |
Jim  | Sam    | Jones | 1/1/11

The json generated looks something like this:

[
  {
    "name" : "Mark",
    "last" : "Jones"
  },
  {
    "name" : "Jim",
    "middle" : "Sam", 
    "last" : "Jones",
    "DOB" : "1/1/11"
  }
]

which messes with other processing needed with the json file – specifically, where a consistent record layout is expected.

I would expect the output to be something like this:

[
  {
    "name" : "Mark",
    "middle" : "",
    "last" : "Jones",
    "DOB" : ""
  },
  {
    "name" : "Jim",
    "middle" : "Sam", 
    "last" : "Jones",
    "DOB" : "1/1/11"
  }
]

Is there any way to tweak JsonExportLine to address this scenario?

Or, maybe I’m just missing something “obvious”! :wink:

TIA!!

2

This is intentional. The short answer is that there is no way to “tweak” this behavior.

Panorama keeps track of which data values are empty. For text data this is ambiguous, but for numbers 0 is different than empty, and date values can also be empty.

JSON does not have any way of expressing an empty value. So when exporting JSON, Panorama simply omits any empty values.

messes with other processing needed with the json file – specifically, where a consistent record layout is expected.

It sounds like you are not doing actual JSON parsing when you are processing this file. If you were doing JSON parsing, there wouldn’t be any problem.

There is no such thing as “consistent” layout for JSON. I’ll bet you’re also expecting the fields to be in the same order every time. There is no guaranteed order for items in a JSON record.

If you want a consistent format, I would suggest you use a consistent format, for example CSV or tab separated.

You could manually create a formula that would export data in a JSON compatible format that was also consistent. Something like this:

"{"+lf()+
|||  "name" : |||+quoted(name)+","+lf()+
|||  "middle" : |||+quoted(middle)+","+lf()+
|||  "last" : |||+quoted(last)+","+lf()+
|||  "DOB" : |||+quoted(datestr(DOB))+","+lf()+
"}"+lf()

Actually, I think the last line is wrong - your DOB field must be a text field I think. If it was a date field the correct formula for exporting a JSON date would be:

|||  "DOB" : |||+quoted(datepattern(DOB,"YYYY-MM-DD)+"T00-00-00Z")+","+lf()+

But that may not be what you want since I don’t think you are actually processing this output with a JSON parser.

Anyway to export the entire database you could use the above formula in an arraybuild statement, then use filesave to export to a text file.

3

This is intentional.

Short response: Well, that explains it then. :slight_smile:

The beauty of JSON is that it can be used as one needs it to be used.

Thank you for the detailed response!