Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update multiple values in a jsonb data in PostgreSQL

I need to update a jsonb data(column->users) in my table 'settings' My jsonb data is like

'{
    "Email": "aaaa",
    "UserId": "49",
    "Created": "11/13/2016",
    "EntityId": "1",
    "IsActive": "False",
    "Modified": "11/13/2016",
    "Username": "aa"
}' 

In this json string I need to update Email,IsActive,Username together. I tried the below update query,its working fine. But that is for a single value updation.

UPDATE settings 
SET users = jsonb_set(users, '{Email}', '"aa"') 
WHERE users @> '{"UserId":"49"}';

How to update for multiple value updation? I am using postgres 9.5.

like image 629
Ajoe Avatar asked Nov 14 '16 07:11

Ajoe


People also ask

How do you update objects in Jsonb arrays with PostgreSQL?

Postgres offers a jsonb_set function for updating JSON fields. The second parameter path defines, which property you want to update. To update items in an array, you can use an index-based approach. To update the first entry in the items array in the example above, a path woud look like this: {items, 0, customerId} .

How do I query Jsonb data in PostgreSQL?

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.

Is Postgres Jsonb fast?

Because JSONB stores data in a binary format, queries process significantly faster. Storing data in binary form allows Postgres to access a particular JSON key-value pair without reading the entire JSON record. The reduced disk load speeds up overall performance.


1 Answers

Use the concatenation operator:

UPDATE settings 
SET users = users || '{"Email": "new email", "IsActive": "True", "Username": "new username"}'
WHERE users @> '{"UserId":"49"}';
like image 103
klin Avatar answered Oct 03 '22 07:10

klin