0

I'm very new to Postgres and trying to insert the array integer values into a table as multiple rows.

Here is the json string

'{   
  
"types": [3,6]
 
}'

trying to read and insert the types into a table with an error

INSERT INTO table(type) SELECT unnest(par_json->'types'::int[]) 

ERROR: malformed array literal: "types" DETAIL: Array value must start with "{" or dimension information.

Appreciate your help !

1 Answer 1

0

That's because JSONB arrays are not formatted the same way as normal integer arrays (jsnob use [] while int[] uses {}).

What you need here is the jsonb_array_elements_text function. I recommend you read the documentation on all JSON/JSONB operators and functions.

As for the solution to your question, this query will correctly "unnest" your values:

select x::int
from jsonb_array_elements_text('{"types": [3,6]}'::jsonb->'types') x;

Since the function returns text you have to manually cast the output to int.

If you'd like to convert a jsonb int array into a normal int[] in postgresql, you may use the following:

select array(select x::int from jsonb_array_elements_text('{"types": [3,6]}'::jsonb->'types') x);

You may even turn this into a function so you don't have to write this code every time yourself.

1
  • Perfect! thanks for your response. Jan 11, 2023 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.