Pat070 Pat070 - 5 months ago 22
JSON Question

Psql function with array of json

I am trying to write a function that has 2 arguments: one array of integers, and one array of JSON

an example of my JSON {[{day:1},{day:2}]}

my function:

create or replace function ctrl_func(theids integer[],thejsons text[])
RETURNS integer AS \$$
DECLARE
i integer;
tj text[];
BEGIN
FOR i IN 1 .. array_upper(theids, 1)
LOOP
raise notice 'ids %',theids[i];
raise notice 'the jsons %',thejsons[i];
// do other stuff but thejsons fail
END LOOP;
END;
\$$ LANGUAGE plpgsql;


Now, I don't understand how to pass the thejsons parameter, I fail miserably in all my trials

Here is what I did

select ctrl_func('{66,67}','{{{{day:1},{day:2}}},{{{day:122},{day:222}}}}');


and the notice message.

NOTICE: ids 66
NOTICE: the jsons <NULL>


I know I am doing something wrong, is it in the passing of parameter or in the selecting thejsons[i] value ? I have been trying a few hours modifying how I pass thejsons, googling like mad, and I cannot find anything that gives me an idea of what to do.

I would be very grateful for any hint, pointer or explanation of what is going on. Thanks in advance

I am adding all my final tests , I have simplified to get the exact problem. I am obviously missing something big somewhere - if anybody can it I would be grateful. Spent part of the night and the whole morning on the docs but no result. Here is what I have so far:

create table tata (json28 jsonb);
insert into tata values('[{"a":2},{"b":3}]');
select * from tata;
json28
----------------------
[{"a": 2}, {"b": 3}]


my new function :

create or replace function trl_toto(thejsons text[]) RETURNS integer AS $$
DECLARE
js1 text;
js text;
BEGIN
raise notice 'the jsons %',thejsons;
raise notice 'the jsons0 %',to_json(thejsons[1]::text);
select regexp_replace(regexp_replace(thejsons[1]::text,'^{{','['),'}}$',']') into js1;
raise notice 'js1 %',js1;
select regexp_replace(regexp_replace(regexp_replace(js1,'^\"',''),'\"$',''),'\\','','g') into js;
raise notice 'js %',js;
update tata set json28=to_json(js)::jsonb;
return 1;
END;
$$ LANGUAGE plpgsql;


Now I use the function

select trl_toto('{"{{{\"day\":1},{\"day\":2}}}","{{{\"day\":122},{\"day\":222}}}"}');
NOTICE: the jsons {"{{{\"day\":1},{\"day\":2}}}","{{{\"day\":122}, {\"day\":222}}}"}
NOTICE: the jsons0 "{{{\"day\":1},{\"day\":2}}}"
NOTICE: js1 [{"day":1},{"day":2}]
NOTICE: js [{"day":1},{"day":2}]


I check the data in the table

select * from tata;
json28
-----------------------------
"[{\"day\":1},{\"day\":2}]"


how can I get rid of the first and last " and of the \" ?
what is wrong when I try to enter js since it has the correct format ?

Answer

A json array is a single value of json or jsonb type. There is no reason to use text[] for it, just use jsonb:

create or replace function example_function(jsondata jsonb)
returns void language plpgsql as $$
begin
    raise notice 'json array: %', jsondata;
    raise notice 'json array first element: %', jsondata->0;
end $$;

select example_function('[{"a":1}, {"b":2}]');

CREATE FUNCTION
NOTICE:  json array: [{"a": 1}, {"b": 2}]
NOTICE:  json array first element: {"a": 1} 

You can use it in a loop with jsonb_array_length():

create or replace function example_function_with_loop(jsondata jsonb)
returns void language plpgsql as $$
declare 
    i int;
begin
    raise notice 'json array: %', jsondata;
    for i in 0 .. jsonb_array_length(jsondata)- 1 loop
        raise notice 'element #%: %', i, jsondata->i;
    end loop;
end $$;

select example_function_with_loop('[{"a":1}, {"b":2}]');

CREATE FUNCTION
NOTICE:  json array: [{"a": 1}, {"b": 2}]
NOTICE:  element #0: {"a": 1}
NOTICE:  element #1: {"b": 2}