Re: JSON and Postgres Variable Queries
От | Andrew Dunstan |
---|---|
Тема | Re: JSON and Postgres Variable Queries |
Дата | |
Msg-id | 53A84899.6080102@dunslane.net обсуждение исходный текст |
Ответ на | Re: JSON and Postgres Variable Queries (Robert Haas <robertmhaas@gmail.com>) |
Список | pgsql-hackers |
On 06/23/2014 11:06 AM, Robert Haas wrote: > On Fri, Jun 20, 2014 at 11:26 AM, Joey Caughey > <jcaughey@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. > Once again, json_data->>’plan'->>’id' is an expression guaranteed to fail, since ->> returns text but expects its left hand o0perand to be json, unlike json_data->’plan'->>’id' or json_data#>>'{plan,id}' So I don't believe the OPs original statement about what is and isn't working. The alias issue, of course, is not at all JSON-specific, and the subselect is one solution - a CTE is another. But you CAN use the alias in an ORDER BY or GROUP BY. cheers andrew
В списке pgsql-hackers по дате отправления: