On Thu, Mar 17, 2016 at 7:19 AM, Krishnakant <[email protected]> wrote:
> Hello,
> I wish to search rows in my table on the basis of text of json keys.
> My table has vouchercode, voucherdate, dramt, cramt.
> Here dramt and cramt are both jsonb fields (postgresql 9.4).
> dramt containes account and amount, same with cramt.
> sample date.
> vouchercode:1 ... dramt{"1":25,"2":25}
> "1" and "2" are account codes.
> there will be several such ros and I want to get only those rows where
> either dramt or cramt contains accountcode as 1.
> Note that accountcode is a key not the value of jsonb data.
> so my sudo code for where is where dramt.key = '1'.
> How can I achieve this?
>
>
The first question would be "how do I write this in SQL?". I've only used
JSONB very briefly so what I say might be wrong here, but this page
describes the postgres JSONB operators:
http://www.postgresql.org/docs/9.4/static/functions-json.html
One way to write your query is to use the "@>" operator:
select *
from yourtable
where dramt @> '{"accountcode": 1}'::jsonb
or cramt @> '{"accountcode": 1}'::jsonb
Another would be the ->> operator:
select *
from yourtable
where dramt ->> 'accountcode' = '1'
or cramt ->> 'accountcode' = '1'
In SQLAlchemy, I think these would be expressed as:
YourTable.dramt.contains({'accountcode': '1'})
and
YourTable.cramt['accountcode'].astext == '1'
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
