From: | Joey Caughey <jcaughey(at)parrotmarketing(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Cc: | gkimball(at)parrotmarketing(dot)com |
Subject: | JSON and Postgres Variable Queries |
Date: | 2014-06-20 15:26:56 |
Message-ID: | 81724D29-A50F-4F77-A2F4-491FB7D39E9B@parrotmarketing.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
Thanks in advance,
Joey
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-06-20 16:22:56 | Re: modify custom variables |
Previous Message | Vincent Mora | 2014-06-20 15:21:38 | modify custom variables |