1

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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.