On 2017-09-07 18:13, Richard PALO wrote:
> Is there any hints on how to use (if supported) the postgresql json array
> support?
>
> I'm a bit at a loss as to how to approach translating something like the
> following
> to python/sql getting a dictionary list appended to each returned row:
>
> > select fy.id, fy.name, fy.date_start, fy.date_stop,
> > ( select array_to_json(array_agg(row_to_json(x)))
> > from ( select j.type, s.name, s.number_next, s.padding, s.prefix
> > from account_journal j
> > join account_sequence_fiscalyear s_fy on s_fy.fiscalyear_id
> > = fy.id
> > join ir_sequence s on s.id = s_fy.sequence_id
> > where j.sequence_id = s_fy.sequence_main_id
> > and j.type in ('sale', 'sale_refund', 'purchase',
> > 'purchase_refund')
> > ) x
> > ) "invoice_sequences"
> > from account_fiscalyear fy
> > order by fy.id asc
>
> can't seem to easily find any pertinent examples using sql arrays or json
> data with python-sql...
>
> Is this feasible with python/sql without resorting to a raw psycopg2 execute
> statement?
ARRAY_AGG does not seem to be in any SQL standard, so it is not
implemented in python-sql. But it is very simple to create it:
from sql.aggregate import Aggregate
class ArrayAgg(Aggregate):
__slots__ = ()
_sql = 'ARRAY_AGG'
ARRAY_TO_JSON is even less in the standard as JSON type is not in the
standard. But it can also easily implemented:
from sql.functions import Function
class ArrayToJson(Function):
__slots__ = ()
_function = 'ARRAY_TO_JSON'
--
Cédric Krier - B2CK SPRL
Email/Jabber: [email protected]
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/
--
You received this message because you are subscribed to the Google Groups
"python-sql" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
