Postgresql 9.5 JSON problems

Question

I have to update a complex query that takes JSON fields:

SELECT grower, fieldname as "Field/Block/Block Name", downloaddate, area, LandType
from
SELECT context->>'Source' as Product,
       context->>'SourceType' as Producttype ....

It uses the Product field at the bottom to search on.

But the original query uses

SELECT context::JSON, r.rboundaryid ...

and this doesn't work, giving a JSON error:

 invalid input syntax for type json

The 'context' field in the table is OK JSON as verified by Jsonlint.

I tried changing to

 SELECT to_json(context) as jcontext ...

changing the top ->> constructs to use the 'jcontext' name ... and this eliminates the error ... but only because the result is NULL, not a JSON object ...

Any ideas on this?

Answer 1

The problem was that the screening part of the query was out of sync with the current database.

The corrected version uses:

context::json as contextobj 

(like my effort)

WHERE typecode = 'IMAGERY'
    AND context != '  '
    AND r.createdon > '1/1/2016'

I didn't have this part.