Блог

Итак, у нас есть поле Jsonb в Postgres, похожее на

 {
   "entity":[
      {
         "id":"1234",
         "data":[
            {
               "docId":"123456",
               "status":"PENDING"
            },
            {
               "docId":"123457",
               "status":"PENDING"
            },
            {
               "docId":"123458",
               "status":"PENDING"
            }
         ]
      }
   ]
}
 

Я хочу обновить объект данных с идентификатором документа = 123457 до статуса «РЕШЕННЫЙ». Не влияя на другие статусы. Как я могу это сделать в Postgres?

Наша первоначальная идея заключалась в том, чтобы прочитать весь документ, а затем переписать объект JSON из Java и повторно сохранить его, но это решение вызывает ошибки race из-за параллельной операции с идентификатором документа 123456, которая завершается за миллисекунды до вызова 123457. Итак, я подумал, может быть, если операция Json обрабатывается в SQL, это может решить эту проблему.

Ваша основная проблема — плохая модель данных. Если бы вы хранили эти данные не как один JSON, а в нормализованной модели базы данных с несколькими таблицами и связями внешнего ключа, упражнение было бы тривиальным.

Вы можете сделать это в SQL, но это, по сути, означает разворачивание JSON в табличные данные, их модификацию jsonb_set и перестройку всего, что требует нескольких боковых соединений и слишком сложно для меня, чтобы получить мотивацию.

Сделайте это на стороне клиента, если это проще. Легко предотвратить условия гонки:

• Один из вариантов — использовать SELECT ... FOR UPDATE всякий раз, когда вы собираетесь изменять данные. Тогда два таких SELECT s будут блокировать друг друга, а второму придется ждать, пока не будет выполнена первая транзакция.
• В данном случае альтернативой может быть использование более высокого уровня изоляции транзакций REPEATABLE READ . Тогда вторая транзакция получит ошибку сериализации при изменении данных и должна будет повторить транзакцию.

Предположим, что вызывается ваша таблица "foo" и jsonb поле "bar" , вы можете сделать это:

 WITH 
e AS (SELECT element, index - 1 AS i 
      FROM foo, jsonb_array_elements(bar -> 'entity') 
      WITH ORDINALITY o (element, index) 
      WHERE element ->> 'id' = '1234'),
f AS (SELECT idx - 1 AS i 
      FROM e, jsonb_array_elements(element -> 'data') 
      WITH ORDINALITY o (elem, idx) 
      WHERE elem ->> 'docId' = '123457')
UPDATE foo
SET bar = jsonb_set(bar, 
                    ARRAY['entity',e.i::text,'data',f.i::text,'status'],
                    '"RESOLVED"', 
                    false) 
FROM e, f 
WHERE bar -> 'entity' -> e.i::int ->> 'id' = '1234';
 

Но, как говорят другие здесь, вы действительно должны преобразовать свои данные в реляционную модель для этого, а не обрабатывать JSON как таблицу: o)