From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
---|---|
To: | Joey Caughey <jcaughey(at)parrotmarketing(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org, gkimball(at)parrotmarketing(dot)com |
Subject: | Re: JSON and Postgres Variable Queries |
Date: | 2014-06-20 17:39:31 |
Message-ID: | 53A471D3.2010102@dunslane.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2014-06-20 19:31:06 | Re: [bug fix] Memory leak in dblink |
Previous Message | Tom Lane | 2014-06-20 17:01:30 | Shouldn't pg_(sh)seclabel.provider be marked NOT NULL? |