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?