Search code examples
jsonpostgresqljsonb

Insert JSON string into Postgres and return field

I want to insert a JSON string into a Postgres table with a jsonb field and want the insert query to return a part of the JSON. For example, I want to return the id in the example below. What goes on the question marks?

insert into mytable (myjson)
values ('{"id":123}') returning ???
Solution

  • Use the ->> operator to extract the value of the id attribute:

    insert into mytable (myjson)
values ('{"id":123}') 
returning (myjson ->> 'id');