On 06/20/2014 11:26 AM, Joey Caughey wrote:
I’m having an issue with JSON requests in Postgres and was wondering
if anyone had an answer.
I have an orders table with a field called “json_data”.
In the json data there is a plan’s array with an id value in them.
{ "plan”: { “id”: “1” } } }
I can do regular queries that will work, like so:
SELECT json_data->>’plan'->>’id' as plan_id FROM orders;
But if I try to query on the data that is returned it will fail:
SELECT json_data->>’plan'->>’id' as plan_id FROM orders WHERE plan_id = 1;
OR
SELECT json_data->>’plan'->>’id' as plan_id FROM orders GROUP BY plan_id;
OR
SELECT json_data->>’plan'->>’id' as plan_id FROM orders ORDER BY plan_id;
Is this something that has been overlooked? or is there another way to
go about this?
I’ve tried everything from the documentation here:
http://www.postgresql.org/docs/9.3/static/functions-json.html
I’ve attached a json dump of the orders table.
The double arrow operators return text, the single arrow operators
return json.
You might also find json_extract_path() useful.
BTW, this is a usage question, and as such should have gone to
pgsql-general, not pgsql-hackers.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
