“JSON text must be an object or array (but found number, string, true, false or null, use allow_nonref to allow this)” about fixmystreet HOT 8 CLOSED

Is it possible to run a query like:

select problem.geocode from problem where problem.id =9999999

where 9999999 is the problem number for one of the failing problems? You could also do select *.

I don't have any FMS back end experience, so I'm going by the code and general SQL principles, and my experience of running SQL queries is with MS SQL Server, not the database used by FMS, so I can't give you precise details of how to run it. I'm assuming that problem.id is the externally visible problem number.

The code is attempting to extract the nearest street from the (reverse) geocoding information captured with the report, and there appears to be something wrong with the structure of that data. The code seems to check for empty values, but it might be that there is a single space, or even byte order marks, rather than an empty value.

from fixmystreet.

Hi @jonkri - what were you updating from? We switched in e8e1c23 to Nominatim for reverse geocoding because Bing started giving inaccurate results. But a bit before then we switched from RABX to JSON for storing the internal extra data columns, which includes geocode - the database migration script should have handled this, but perhaps something went wrong in some way when doing so. It does sound like the issue is more likely to be the RABX to JSON switch, the OSM switch didn't change the decoding of the data. But then I would assume something else would be erroring, not just the alert sending. We'll probably need to know what is stored in the database that it's erroring on, plus then if that's happening with new reports work out where/why it's storing bad data, as that shouldn't happen. We haven't had this ourselves as far as I can see, so I'd hope the storing code is okay, but hard to say at this point.

from fixmystreet.

Hi! 👋

The code is attempting to extract the nearest street from the (reverse) geocoding information captured with the report, and there appears to be something wrong with the structure of that data. The code seems to check for empty values, but it might be that there is a single space, or even byte order marks, rather than an empty value.

There are both NULL values and “null” strings in the database.

# SELECT problem.geocode FROM problem GROUP BY problem.geocode;
 geocode 
---------
         
 null    
(2 rows)

There are 676 NULL values and 374 “null” strings:

# SELECT COUNT(problem.geocode) FROM problem WHERE problem.geocode='null';
 count 
-------
   374
(1 row)

# SELECT COUNT(1) FROM problem WHERE problem.geocode IS NULL;
 count 
-------
   676
(1 row)

Are the “null” strings wrong here? In other words, should something like this fix the database?

# UPDATE problem SET problem.geocode = NULL WHERE problem.geocode = 'null';

what were you updating from?

This is the output from the migration after which this started occuring.

Current database version = 0081
0082-add-json-columns
............
Migrating data for table body - 5 rows to migrate
5/5
Migrating data for table contacts - 628 rows to migrate
628/628
Migrating data for table problem - 374 rows to migrate
374/374
Migrating data for table comment - 48 rows to migrate
48/48
Migrating data for table users - 125 rows to migrate
125/125
Migrating data for table moderation_original_data - 2 rows to migrate
2/2
Migrating data for table defect_types - 0 rows to migrate

Migrating data for table report_extra_fields - 1 rows to migrate
1/1
Migrating data for table roles - 2 rows to migrate
2/2
Migrating data for table token - 1975 rows to migrate
1975/1975
0083-set-token-data-json-not-null
...
0084-rename-json-columns

Thank you!

Edit: Removed , COUNT(problem.geocode) from the first statement since its output indicated that there were zero NULL values in the database (since NULL values don't add to the count).

from fixmystreet.

There are both NULL values and “null” strings in the database.

That shouldn't be the issue - the difference here is NULL means the value is unknown, whereas "null" JSON data means it has fetched some data and that data was empty. Perhaps this should behave differently, but I don't think this matters for this issue - fetching $p->geocode in both cases would return undef. In Alerts.pm, the line before line 460 that you quote returns early if $geocode is undefined, so no null value will be reaching the line that causes the error. Also if a null reached line 460, the error would be "malformed JSON string", not the error that you received. So I think it is something else stored in the geocode column that is causing the issue.

I think a SQL like SELECT id,geocode FROM problem WHERE jsonb_typeof(geocode) NOT IN ('object', 'null'); will show you all the rows that contain a non-object geocode entry. I don't get any results in our live database for that query.

Separately, though, it appears our server does not give the same error on scalar entries:

$ bin/cron-wrapper perl -MData::Dumper -MJSON::MaybeXS -e'my $t = "\"test\""; print Dumper(JSON->new->decode($t));'
$VAR1 = 'test';

But I do get the error on the same code locally:

$ bin/cron-wrapper perl -MData::Dumper -MJSON::MaybeXS -e'my $t = "\"test\""; print Dumper(JSON->new->decode($t));'
JSON text must be an object or array (but found number, string, true, false or null, use allow_nonref to allow this) at -e line 1.

So it appears the underlying JSON module being used (decided by whatever method) may have different default behaviour, which is quite annoying. What do you get if you run: bin/cron-wrapper perl -MJSON::MaybeXS -e'print JSON() . "\n"'
Here I get JSON::XS on the server, and Cpanel::JSON::XS locally.

from fixmystreet.

I think you are confusing "null", with "" here. "null" will pass as true on the line before 460, and is one of the things that the things the error message says was found, so is consistent with the error message.

The OP is saying they got 374 "null"s, 0 ""s and 676 database NULLs.

from fixmystreet.

Good morning!

I'm getting Cpanel::JSON::XS:

$ bin/cron-wrapper perl -MJSON::MaybeXS -e'print JSON() . "\n"'
Cpanel::JSON::XS

As expected, I'm getting the error when running the Dumper command as well:

$ bin/cron-wrapper perl -MData::Dumper -MJSON::MaybeXS -e'my $t = "\"test\""; print Dumper(JSON->new->decode($t));'
JSON text must be an object or array (but found number, string, true, false or null, use allow_nonref to allow this) at -e line 1.

There are no rows in the database where the type of geocode is something other than object or null:

# SELECT id,geocode FROM problem WHERE jsonb_typeof(geocode) NOT IN ('object', 'null');
 id | geocode 
----+---------
(0 rows)

(I removed , COUNT(problem.geocode) from the first statement in #4470 (comment) since its output indicated that there were zero NULL values in the database (since NULL values don't add to the count).)

from fixmystreet.

@davidw65 The column is JSONB, so the string null in the DB is normally returned to the code as an undefined value, because that's what it is as JSON. For the string "null" to be available to the code, the column in the database would contain "null" (with the quotes).

from fixmystreet.

@jonkri Thanks - okay, so the underlying Cpanel::JSON::XS has a different default from JSON::XS, which explains why you get an error and our server does not. I've pushed a fix to allow_nonref here as well (and checked the other times this type of thing is used), so hopefully that's resolved the issue for you now.

from fixmystreet.