Psql function with array of json

Question

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 ?

'{{{{day:1},{day:2}}},{{{day:122},{day:222}}}}' looks like a text literal, not a text[] literal, to me. Have you tried something like '{"{{{day:1},{day:2}}}","{{{day:122},{day:222}}}"}'? — jmelesky
– jmelesky, Commented Jun 20, 2016 at 18:27
yes - now I can see thejsons[i] . Now I need to find out how to transform that to a Json so that I can use it in the rest of the query. will continue in this direction to solve the problem. thanks for the pointer — Pat070
– Pat070, Commented Jun 20, 2016 at 20:23
I still cannot do much in the rest of my function. I would like to use my son. i sql I can do: update tata set jsoncol='[{day:1},{day:2}]' and it works; but in the function I get "{{{day:1},{day:2}}}" How do I transform "{{ to [ ? (should I add my tests in my first question ?) — Pat070
– Pat070, Commented Jun 21, 2016 at 10:34

klin · Accepted Answer · 2016-06-21 12:03:17Z

1

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}

edited Jun 21, 2016 at 12:03

answered Jun 21, 2016 at 11:47

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pat070 Over a year ago

Thanks, I will look into it. My function now works after a lot of regex. The data are passed from node, I will study your solution to see how I can simplify everything.

Collectives™ on Stack Overflow

Psql function with array of json

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related