0

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?

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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