Menu
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 ???
741
The simplest way to return JSON is with row_to_json() function. It accepts a row value and returns a JSON value. select row_to_json(words) from words; This will return a single column per row in the words table.
Some of the popular Operators useful for inserting JSON into PostgreSQL are: -> Operator: It enables you to select an element from your table based on its name. Moreover, you can even select an element from an array using this operator based on its index.
Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
jsonb elements can be of the following types: Objects. Arrays. String.
Use the ->> operator to extract the value of the id attribute:
insert into mytable (myjson)
values ('{"id":123}')
returning (myjson ->> 'id');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
