JSON and Postgres Variable Queries

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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers