Insert JSON string into Postgres and return field

json postgresql jsonb
Michiel Borkent picture Michiel Borkent · Oct 3, 2016 · Viewed 18.7k times · Source

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 ???

Answer

a_horse_with_no_name picture a_horse_with_no_name · Oct 3, 2016

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

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

Related questions

Query for array elements inside JSON type

I'm trying to test out the json type in PostgreSQL 9.3. I have a json column called data in a table called reports. The JSON looks something like this: { "objects": [ {"src":"foo.png"}, {"src":"bar.png"} ], "background":"background.png" } I would …

Read More
Explanation of JSONB introduced by PostgreSQL

PostgreSQL just introduced JSONB and it's already trending on hacker news. It would be great if someone could explain how it's different from Hstore and JSON previously present in PostgreSQL. What are its advantages and limitations and when should someone …

Read More
Index for finding an element in a JSON array

I have a table that looks like this: CREATE TABLE tracks (id SERIAL, artists JSON); INSERT INTO tracks (id, artists) VALUES (1, '[{"name": "blink-182"}]'); INSERT INTO tracks (id, artists) VALUES (2, '[{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]'); …

Read More