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

SELECT grower, fieldname as "Field/Block/Block Name", downloaddate, area, LandType
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?

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.