Re: [GENERAL] JSON to INT[] or other custom type

[Rory Campbell-Lange]

On 11/06/17, Bruno Wolff III (br...@wolff.to) wrote:
> On Sun, Jun 11, 2017 at 22:35:14 +0100,
>  Rory Campbell-Lange  wrote:
> >
> >I'm hoping, in the plpgsql function, to unfurl the supplied json into a
> >custom type or at least an array of ints, and I can't work out how to do
> >that.
> >
> >   select * from json_array_elements_text('[[0, 1], [1, 2]]');
> >value
> >   
> >[0, 1]
> >[1, 2]
> >   (2 rows)
> >
> >works fine, but I can't seem to turn those values into actual ints or
> >anything else for that matter, apart from text via the
> >json_array_elements_text() function.
> 
> Does this example help?
> 
> area=> select (a->>0)::int, (a->>1)::int from json_array_elements('[[0, 1], 
> [1, 2]]') as s(a);
> int4 | int4 --+--
>0 |1
>1 |2
> (2 rows)

Hi Bruno

That worked great, thank you very much for the pointer.
I completely failed to see the top of the docs at
https://www.postgresql.org/docs/9.5/static/functions-json.html
-- my apologies.

I've put a working function and caller from PHP, Python below.

Many thanks
Rory

/* plpgsql test function */
CREATE OR REPLACE FUNCTION fn_test05 (
num INT
,ds json
) RETURNS SETOF dow_session AS $$
DECLARE
r test.dow_session;
BEGIN
RAISE NOTICE 'num: %', num;

FOR r IN 
SELECT
(n->>0)::int -- first json element
,(n->>1)::int
FROM
json_array_elements(ds)
AS x(n)
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;


## python example
import psycopg2
import json
dbc = psycopg2.connect(database=, user=, password=)
cur = dbc.cursor()
query = 'select * from test.fn_test05(%s, %s)'
a_of_a = json.dumps([(0,2), (3,1), (5,2)])
qargs = (5, a_of_a)
cur.execute(query, qargs)
rs = cur.fetchall()
for r in rs:
print r
# (0, 2)
# (3, 1)
# (5, 2)


## php example
 user= password=");
$query = 'select * from test.fn_test05($1, $2)';
$result = pg_query_params($dbconn, $query, array($a, $j));
print_r(pg_fetch_all($result));
# Array
# (
# [0] => Array
# (
# [dow] => 0
# [session] => 1
# )
# 
# [1] => Array
# (
# [dow] => 2
# [session] => 3
# )
# )
?>



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JSON to INT[] or other custom type

[David G. Johnston]

On Sun, Jun 11, 2017 at 2:35 PM, Rory Campbell-Lange <
r...@campbell-lange.net> wrote:

> I'm hoping, in the plpgsql function, to unfurl the supplied json into a
> custom type or at least an array of ints, and I can't work out how to do
> that.


​You will be unable to cast the array itself.  You must use json functions
to unfurl the json into a result set with each row containing a single
value.  You can then explicitly cast that value to integer.  If the casting
doesn't fail you now have column of integers that can be
"array_agg(value_as_integer)​" to construct an SQL array of type integer[]
(or whatever you decide to cast it to).

You can write you own pl/pgsql function to do these operations.

As your example includes a two-dimensional array you probably will need
to json_array_elements_text twice, then array_agg twice, to get a similar
structure in the eventual SQL array.

Hopefully that moves you in a useful direction.

David J.

Re: [GENERAL] JSON to INT[] or other custom type

[Bruno Wolff III]

On Sun, Jun 11, 2017 at 22:35:14 +0100,
 Rory Campbell-Lange  wrote:


I'm hoping, in the plpgsql function, to unfurl the supplied json into a
custom type or at least an array of ints, and I can't work out how to do
that.

   select * from json_array_elements_text('[[0, 1], [1, 2]]');
value
   
[0, 1]
[1, 2]
   (2 rows)

works fine, but I can't seem to turn those values into actual ints or
anything else for that matter, apart from text via the
json_array_elements_text() function.


Does this example help?

area=> select (a->>0)::int, (a->>1)::int from json_array_elements('[[0, 1], [1, 
2]]') as s(a);
int4 | int4 
--+--

   0 |1
   1 |2
(2 rows)



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] JSON to INT[] or other custom type

[Rory Campbell-Lange]

I'm playing with plpgsql function parameters to try and come up with a
neat way of sending an array of arrays or array of custom types to
postgres from python and PHP. 

Psycopg works fine with an array of custom types:

In [108]: query = 'select * from fn_test03(%s, %s::dow_session[])'
In [109]: qargs = (5, [(0,2), (1, 3)])
In [110]: cur.execute(query, qargs)   
In [111]: rs = cur.fetchall()  # function plays back looped arrays
In [112]: rs
Out[112]: [(0, 2), (1, 3)]

However PHP's Postgresql support isn't very array friendly as far as I
can see, and requires some horrible manual escaping.

Consequently I wondered if I could serialise the array structure into
json and send that to the postgresql function. PHP seems to handle that
fine. My problem therefore move to handling the json in the plpgsql
function.

I'm hoping, in the plpgsql function, to unfurl the supplied json into a
custom type or at least an array of ints, and I can't work out how to do
that.

select * from json_array_elements_text('[[0, 1], [1, 2]]');
 value  

 [0, 1]
 [1, 2]
(2 rows)

works fine, but I can't seem to turn those values into actual ints or
anything else for that matter, apart from text via the
json_array_elements_text() function.

=>  select n::integer[]
from
json_array_elements_text('[[0, 1], [1, 2]]') as x(n);

ERROR:  malformed array literal: "[0, 1]"
DETAIL:  Missing "]" after array dimensions.

=>  select n::text[]::integer[]
from
json_array_elements_text('[[0, 1], [1, 2]]') as x(n);

ERROR:  malformed array literal: "[0, 1]"
DETAIL:  Missing "]" after array dimensions.

Thoughts gratefully received;
Rory



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general