Thread: JSON and Postgres Variable Queries
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:
I’ve attached a json dump of the orders table.
Thanks in advance,
Joey
Attachment
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
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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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
Robert Haas <robertmhaas@gmail.com> writes:
> 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,
Standards compliance? It's not just a trivial syntactic issue either,
but a rather fundamental conceptual one: expressions in the target list
are not supposed to be evaluated until after the WHERE clause is.
regards, tom lane