JSON and Postgres Variable Queries

From: Joey Caughey <jcaughey(at)parrotmarketing(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: gkimball(at)parrotmarketing(dot)com
Subject: JSON and Postgres Variable Queries
Date: 2014-06-20 15:26:56
Message-ID: 81724D29-A50F-4F77-A2F4-491FB7D39E9B@parrotmarketing.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Thanks in advance,

Joey

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2014-06-20 16:22:56 Re: modify custom variables
Previous Message Vincent Mora 2014-06-20 15:21:38 modify custom variables