INSERT array elements from JSON object as table rows

Question

I need to insert below JSON value into a table which will have two rows as id 1708,1 and 1708,2:

{
"id":1708,
"types":[1,2]
}

Below is what I have tried:

insert into table(1708,
unnest(array(select json_array_elements_text('{
"id":1708,
"types":[1,2]
}'::json->'types'))

This is complaining for type of types as integer. However, the query also does not look efficient to me. Could someone guide me on how to do this properly?

Answer 1

Something like this:

INSERT INTO tbl (id, type)  -- spell out actual column names!
SELECT 1708, json_array_elements_text('{"id":1708, "types":[1,2]}'::JSON -> 'types');

Can't be sure since your question does not disclose table definition and Postgres version. Assuming at least Postgres 10. And you may need data type casts.

You need a SELECT. A VALUES expression would not allow a set-returning function.

Spell out target columns for persisted INSERT statements. Or the statement easily (and maybe silently) breaks when changing table columns.