score:1
Accepted answer
but at the moment it is to update the value at 0 index
That can be done using an index based "path" for jsonb_set()
update bo_user
set "json" = jsonb_set("json", '{roles,0}'::text[], '"SYSTEM_ADMINISTRATOR"')
where "json" #>> '{roles,0}' = 'Admin'
The "path" '{roles,0}'
references the first element in the array and that is replaced with the constant "SYSTEM_ADMINISTRATOR"'
Note the double quotes inside the SQL string literal which are required for a valid JSON string
The WHERE clause ensures that you don't accidentally change the wrong value.
score:0
So this worked.
UPDATE public.bo_user
SET json = jsonb_set(json, '{roles}', ('["SYSTEM_ADMINISTRATOR"]')::jsonb, true)
where id = '??';
More questions
- Postgres - updating an array element in a json column
- POSTGRES JSON: Updating array value in column
- Postgres index creation for jsonb column having JSON Array value
- creating json array using column name as key and column as value in postgres
- Extract an array from a postgres json column and map it
- Filter postgres JSON column by null value in SQLAlchemy
- SQLAlchemy set default value for postgres JSON column
- How to parse json value of text column in postgres
- Postgres JSON column updating individual keys/attributes with form
- Query data inside an attribute array in a json column in Postgres 9.6
- Merging an array of JSON objects to one JSON column in Postgres
- How can Postgres unpack a json array into a column then re-pack it, but preserve the data type?
- updating column with value from another column in postgres table
- Postgres Group by json array column
- Converting json array value to multiple rows using json_array_elements in postgres
- Updating a jsonb array value with constraints in Postgres
- Select Unique value from a JSON Array - PostgreSQL JSON column
- query within a json array of strings column in postgres with objection.js or knex.js
- Updating a column value of type json failed with an exception
- Postgres Build Complex JSON Object from Wide Column Like Design to Key Value
- Postgres how to find rows where in jsonb type column contains key with array which contain some value with or condition
- Postgres Where Value in Json Array of Objects
- How to add a key value pair in Json type column in Postgres
- What to insert into Postgres json type column if value is None
- Trying to pass json hash to postgres json column results in null value
- Ecto query Postgres JSON Array for a value
- multiply a value of each item of a json array with postgres 9.6
- Update new column with value from jSON in the same row Postgres
- Updating JSON in postgres based on dynamic input of type json array
- How to remove a value from a postgres text column json string array?
More questions with similar tag
- JPQL IN clause with @Convert
- ASP.NET postgresql NpgsqlDbType.Bit doesn't support BitArray?
- How to coalesce an empty/null value that is part of Concact string in Postgres?
- SQL - Selecting all rows with non matching null rows
- Group by hour, aggregate average
- Postgresql Explain Plan Differences
- Missing FROM-clause entry for a table
- updating table rows in postgres using subquery with jooq
- Update from selection, otherwise revert to other selection if empty in PostgreSQL
- PostgreSQL - Use event trigger to validate a trigger
- Getting datas from past months
- Sequelize Error 42804. will need to rewrite or cast the expressions
- PDO Postgres - Check if Column Exist and do action based off condition
- Pgpass not working in unixODBC/pyodbc
- Get results from HQL query with the same order as given list
- keep getting this error{relation "profiles_user" does not exist}
- What is the difference between `ARRAY[(value'::text]` and `ARRAY['value']::text[]`
- How to implement a boolean OR update instraction?
- How to change SQL Server instead of delete trigger to PostgreSQL
- How add a new value to an enum in Liquibase?
- Postgresql pattern matching
- PostgreSQL How to put constraint on column of type arrays
- Prisma 1-1 relationship can't access data from both sides
- Index on Timestamp: Functions in index expression must be marked as IMMUTABLE
- Install pg_partman on macOS
- How to create an index on a function in PostgreSQL?
- Postgres duplicate error
- Set database provider for Web Deploy
- Not saving unique id in different table - Rails 4
- How to match phone number prefix to country from phonenumber in SQL
Source:
stackoverflow.com