On Thursday 17 March 2016 03:46 PM, Simon King wrote:
On Thu, Mar 17, 2016 at 7:19 AM, Krishnakant <[email protected]
<mailto:[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
Thanks a million Simon this is wonderful.
Now I have another issue related to this.
Suppose I wish to sum up all the amounts for accountcode 1 if it appears
in any Dramount dictionary of any voucher row.
Will this be possible at all?
dramount is in every row of voucher and I want to sum up for the total
so that I see how much transactions have happened.
Happy hacking.
Krishnakant.
--
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.
