How to read integer array values coming in JSON parameter in Postgres - Missing "]" after array dimensions

Question

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 !

Answer 1

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.