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?


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, privacy policy and cookie policy

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