arrays and json with python-sql

[Richard PALO]

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?

cheers,

--

Richard PALO

[Richard PALO]

Le 07/09/2017 à 18:13, Richard PALO a écrit :
> 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
>

even simpler with pg 9.5+ where 'array_to_json(array_agg(row_to_json(x)))' => 'json_agg(x)' is available.


--

Richard PALO

[Cédric Krier]

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: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Richard PALO]

Le 07/09/2017 à 19:12, Cédric Krier a écrit :
> 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'
>

Okay, super, I believe I got much further with this.

> class JsonAgg(Aggregate):
> __slots__ = ()
> _sql = 'json_agg'
>
> fy = Table('account_fiscalyear')
> j = Table('account_journal')
> s = Table('ir_sequence')
> s_fy = Table('account_sequence_fiscalyear')
>
> subquery = j.join(
> s_fy,
> condition=s_fy.fiscalyear_id == fy.id)
> subquery = subquery.join(
> s,
> condition=s.id == s_fy.sequence_id)
> subquery = subquery.select(
> j.type, s.name, s.number_next, s.padding, s.prefix,
> where=(j.sequence_id == s_fy.sequence_main_id) &
> (j.type.in_(['sale', 'sale_refund', 'purchase', 'purchase_refund'])))
>
> x = With(query=subquery)
> invoice_sequences = x.select(As(JsonAgg(x), 'invoice_sequences'), with_=[x])
>
> query = fy.select(
> fy.id, fy.name, fy.date_start, fy.date_stop, invoice_sequences,
> order_by=fy.id.asc)
>
> print(query)
> cur.execute(*query)
>
> for x in cur:
> print(x.id, x.name, x.date_start, x.date_stop, x.invoice_sequences)

The resulting (reformatted) query is:
> SELECT "a"."id", "a"."name", "a"."date_start", "a"."date_stop",
> ( WITH "b" AS (SELECT "c"."type", "e"."name", "e"."number_next", "e"."padding", "e"."prefix"
> FROM "account_journal" AS "c"
> INNER JOIN "account_sequence_fiscalyear" AS "d" ON ("d"."fiscalyear_id" = "a"."id")
> INNER JOIN "ir_sequence" AS "e" ON ("e"."id" = "d"."sequence_id")
> WHERE (("c"."sequence_id" = "d"."sequence_main_id")
> AND ("c"."type" IN (%s, %s, %s, %s))))
> SELECT json_agg("b") AS "invoice_sequences" FROM "b" AS "b")
> FROM "account_fiscalyear" AS "a"
> ORDER BY "a"."id" ASC

which is what, after a slight rework of the initial psql, seemed the most logical solution at this point

This is the psql:

> select fy.id, fy.name, fy.date_start, fy.date_stop,

> ( with x as (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')
> )

> select json_agg(x.*) as "invoice_sequences" from x)

> from account_fiscalyear fy
> order by fy.id asc

I should now be able to get the json aggregate into a dictionary list.

Cheers

--

Richard PALO