On Fri, Mar 18, 2016 at 5:12 AM, Krishnakant <krm...@openmailbox.org> wrote:
>
>
> On Thursday 17 March 2016 03:46 PM, Simon King wrote:
>
> On Thu, Mar 17, 2016 at 7:19 AM, Krishnakant <krm...@openmailbox.org>
> 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.
>
I think you should be able to use something like:
sa.func.sum(YourTable.dramt['amount'].cast(sa.Float))
ie.
result =
(session.query(sa.func.sum(YourTable.dramt['amount'].cast(sa.Float))
.filter(YourTable.dramt['accountcode'].astext == '1')).scalar()
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
