Update specific record inside jsonb column containing multiple objects-CodePudding

Consider the column named "DocumentInformation" of type jsonb having this specific record:

[
 {
    "SchoolsCode": 22,
    "SchoolsName": "Home School",
  },
  {
    "SchoolsCode": "101770",
    "SchoolsName": "Blossom Senior High School",
  }
]

Here's the postgresql query I was trying with, to update the value of schoolCode on the basis of SchoolName.

Update SchoolRecords set DocumentInformation = jsonb_set(documentInformation, '{schoolCode}', '"00001"') where documentInformation ->> 'SchoolName' = 'Home School'

But getting the fail response as:

UPDATE 0

Query returned successfully in 401 msec.

CodePudding user response：

You have to find the index of the arrays to be modified and then modify it with jsonb_set like that:

with my_json as (
select ('{'||index-1||',SchoolsCode}')::text[] as path
from school_records,
jsonb_array_elements(document_information) with ordinality arr(di,index)
 where di->> 'SchoolsName'='Home School'
)
update school_records set document_information = jsonb_set(document_information,my_json.path,'"000001"')
from my_json;

Result here