Question 229556 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – unable to assign text[] value to variable in pgsql

raise notice 'output %', arr[i]->>val;
data_ = arr[i]->>val::text[];

raise notice is giving proper output as

["cust_full_name","cust_email"]

but when 2nd line is giving error when executing

SQL Error [42883]: ERROR: operator does not exist: json ->> text[]
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

where data_ is type text[] and
arr[i]->>val => val is variable which is conaining dynamic key of object which is string.

i am trying to execute above statements in pgsql of procedural function.

i tried various inbuild methods of pgsql but none of them worked such as

data_ = jsonb_array_elements(arr[i]->>val);

data_ = jsonb_array_elements_text(arr[i]->>val);
Tags:

2

Answers


  1. 0

    You probably meant to do (arr[i]->>val)::text[], not arr[i]->>(val::text[])? But this wouldn’t work either, if arr[i]->>val is the text representation of a JSON array, this won’t be valid representation of a postgres text array – you’d still get a runtime exception because it cannot be parsed as a literal.

    If data_ should have type json(b), then you should use -> insted of ->>:

    data_ = arr[i] -> val;

    If data_ should have type text[], then you’ll need to create a postgres array from the string elements of the JSON array:

    data_ = ARRAY(SELECT value FROM json_array_elements_text(arr[i] -> val));
    Login or Signup to reply.
  2. 0

    Operator :: has higher priority than operator ->>, so it is evaluated first, and next it should to fail, because ->> operator expects string type on right side. Unfortunately, there is not any prepared cast from json to any array, so you should to use workaround like proposed @Bergi.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search