Re: JSON to INT[] or other custom type

On 11/06/17, Bruno Wolff III (bruno(at)wolff(dot)to) wrote:
> On Sun, Jun 11, 2017 at 22:35:14 +0100,
> Rory Campbell-Lange <rory(at)campbell-lange(dot)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
# )
# )
?>