On Fri, Jun 20, 2014 at 11:26 AM, Joey Caughey
<jcaug...@parrotmarketing.com> 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?You might find a sub-SELECT helpful: SELECT * FROM (SELECT json_data->>’plan'->>’id' as plan_id FROM orders) x WHERE plan_id = 1 It might be a generally useful thing for WHERE-clause items to be able to reference items from the target list by alias, or maybe it's problematic for some reason that I don't know about, but right now they can't. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
