CODEWITHSUNDEEP
arraysjsonpostgresql
Pat070
Pat070

Reputation: 335

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 ?

Upvotes: 2

Views: 1196

Answers (1)

klin
klin

Reputation: 121474

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}

Upvotes: 1

Related Questions