Downloading data with null value as JSON causes an Internal Server Error

[bpeXX]

CKAN version
2.8.4 and 2.8.10

Describe the bug
When downloading data (which is stored in the datastore) as JSON, an internal server error occurs if the data contains a null value. The error occurs when each row has at least one column with a null value.

Steps to reproduce
The bug can be reproduced via ckan API:

1. Create resource: http://myserver:myport/api/3/action/resource_create
   with body form data: package_id (ID of an existing dataset) and name (name of resource)
2. Create datastore and add data: http://myserver:myport/api/3/action/datastore_create
   with body raw data:
   { "resource_id": "54317d3a-3a26-4911-911d-57c615639756", "force": "true", "fields": [ { "id": "id", "type": "numeric" }, { "id": "name", "type": "text" } ], "primary_key": "id", "records": [ { "id": "1", "name": null } ] }
3. Download data as JSON: http://myserver:myport/datastore/dump/54317d3a-3a26-4911-911d-57c615639756?format=json

Expected behavior
A download should be possible without error. In this particular case the resulting json file will be empty. (In case of multiple rows of data, ckan already ignors the rows with null values and downloads the rest of data successfully.)

Additional details
Stack trace:

[robin-NEC]

Hi @wardi @bpeXX , should we allow users to upload files with null values? I tried to reproduce the issue but received a different error that is associated with the #6713 issue, I've fixed issue #6713 and will raise the PR soon.

But I found that when downloading a resource in json format, this query runs. I found that when concatenating with the || operator, the values from a row that contains a null value, the output for that particular row changes into a blank row, I've tested with multiple queries containing null values, I believe this is the default behavior of PostgreSQL.

We can have a different approach where we can replace the null value with another value, but that would be an alteration of the file itself.
I believe a better approach would be to raise a ValidationError message if a null value is found in the file.

@wardi, I'd like to know your opinion on this.

[kowh-ai]

Hi @bpeXX - Just wondering if you tested this issue with CKAN 2.9 (2.9.5 or master)?

[bpeXX]

Hi @kowh-ai, unfortunately I did not test the issue with CKAN 2.9 since our application is based on CKAN 2.8.4.

[KatiRG]

@bpeXX @kowh-ai I am having the same issue on CKAN 2.9.7
left a comment in issue page:
#6713 (comment)