Thread: [GENERAL] JSON to INT[] or other custom type
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
On Sun, Jun 11, 2017 at 22:35:14 +0100,
Rory Campbell-Lange <rory@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.
>
> 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)
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.
On 11/06/17, Bruno Wolff III (bruno@wolff.to) wrote:
> On Sun, Jun 11, 2017 at 22:35:14 +0100,
> Rory Campbell-Lange <rory@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.
> >
> > 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=<db>, user=<user>, password=<pass>)
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
<?php
$a = 77;
$b = array(array(0,1), array(2,3));
$j = json_encode($b);
$dbconn = pg_connect("dbname=<db> user=<user> password=<pass>");
$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
# )
# )
?>