Как правильно проапдейтить поле JSON?

Question

[Elvis]

Есть таблица с полями id, ticket_id, task_id, mycontent.
первые три поля - числа, последнее jsonb
на каждый ticket_id выводит две или больше строки с разным task_id.
нужно проапдейтить поле mycontent, изменив в json по определенному пути значение в виде "ticket_id:task_id", причем task_id должен быть минимальным из всех строк(по id), а изменения должны быть только в максимальном из всех строк(по id).
Пробовал как то так:

update myorders
set mycontent= jsonb_set(mycontent, '{orders, initialTaskId}', to_jsonb(concat(ticket_id, ':', (select min(xx.task_id) from myorders xx where xx.ticket_id=ticket_id))::text), false)
where id = (select t.id 
			from myorders t 
			where t.ticket_id=1792829 
			order by t.id desc 
			limit 1)

Однако в итоге в итоговом значении в task_id выбирается минимальный из всей таблицы(т.е. вид получается такой: "1792829:1", а должно "1792829:14709539"), а не из тех строк, что по условию.
Пример таблицы:

id    |ticket_id|task_id |mycontent                                      
------+---------+--------+-------------------------------------------------
106849|  1792829|14709539|{"orders": {"initialTaskId": "1792829:14709539"}}
108057|  1792829|14740625|{"orders": {"initialTaskId": null}}

Если выполнить типо такого, то выводит всё правильно:

select jsonb_set(mo.mycontent, '{orders, initialTaskId}', to_jsonb(concat(mo.ticket_id, ':', (select min(xx.task_id) from myorders xx where xx.ticket_id=mo.ticket_id))::text), false)
from myorders mo 
where mo.ticket_id = 1792829
order by mo.id desc
limit 1

Прошу помощи поправить скрипт UPDATE.

Comments

[mayton2019]

Непонятно. Пример таблицы - это как должно быть или как оно щас выводит?

[Elvis]

mayton2019, это как сейчас. по сути в {orders, initialTaskId} последнего task_id должно быть тоже самое что и в первом, если опираться на id при определении первого и последнего.

[mayton2019]

Elvis, ты мог бы сделать одолжение и напечатать две таблички? Как надо. И как оно сейча работает. Потому что из путанного описания вопроса трудно достать факты.

[Elvis]

mayton2019, да, конечно.
Вот исходные данные:

id    |ticket_id|task_id |mycontent                                      
------+---------+--------+-------------------------------------------------
106849|  1792829|14709539|{"orders": {"initialTaskId": "1792829:14709539"}}
108057|  1792829|14740625|{"orders": {"initialTaskId": null}}

Вот то что сейчас получается:

id    |ticket_id|task_id |mycontent                                      
------+---------+--------+-------------------------------------------------
106849|  1792829|14709539|{"orders": {"initialTaskId": "1792829:14709539"}}
108057|  1792829|14740625|{"orders": {"initialTaskId": "1792829:1"}}

Вот то что должно быть:

id    |ticket_id|task_id |mycontent                                      
------+---------+--------+-------------------------------------------------
106849|  1792829|14709539|{"orders": {"initialTaskId": "1792829:14709539"}}
108057|  1792829|14740625|{"orders": {"initialTaskId": "1792829:14709539"}}

[mayton2019]

Elvis, если забыть на секундочку про JSON то получается что нам надо просто применить оконную функцию
max(task_id) где окном будет ticket_id. Верно?

[Elvis]

mayton2019, да, получается что так. если я правильно понял смысл оконной функции(только сейчас прочитал о ней:)

[mayton2019]

Elvis, реши задачу на оконных функциях. А потом json просто сгенерируешь с нуля.
Тоесть тебе не нужно делать update а просто создавай новый документ.

[Elvis]

mayton2019, дело в том что в json могут быть и другие ключи словаря со своими значениями, в которых нужно сохранить значения, поэтому пошел методом update. грубо говоря из-за бага не заполнилось значение ключа, а остальное заполнилось и нужно проапдейтить таблицу.

[mayton2019]

Elvis, ок. Вот твой кейс сразу сверху идет.

https://www.postgresql.org/docs/current/tutorial-w...

[Elvis]

mayton2019, Видоизменил запрос, но всё равно ставит значение такое же

update myorders
set content = jsonb_set(content , '{orders, initialTaskId}', to_jsonb(concat(ticket_id, ':', (select min(xx.task_id) over (partition by ticket_id) from myorders xx limit 1))::text), false)
where id = (select max(t.id) from myorders t where t.ticket_id=1792829)

добавил limit 1 так как

select min(xx.task_id) over (partition by ticket_id) from myorders xx

возвращало 2 одинаковых значения(но возвращает то что нужно).

[Elvis]

mayton2019, хорошо, можно эту задачу решить по другому. Нужно взять {orders, initialTaskId} из минимального id и по тому же ключу заапдейтить значение у максимального id. возможно так будет проще?

[Elvis]

mayton2019, Вот этот скрипт решает мою задачу

update myorders
set content = jsonb_set(content, '{orders, initialTaskId}', to_jsonb((select t.content #>> '{orders, initialTaskId}' from myorders t where t.id = (select min(t.id) from myorders t where t.ticket_id=1792829))::text), false)
where id = (select max(t.id) from myorders t where t.ticket_id=1792829)

НО! тут в двух местах нужно прописать ticket_id, а у меня там будет предполагаться in с перечислением многих идентификаторов. получается пришел к тому что нужно делать цикл.

[mayton2019]

Твоюж дивизию... Оконные функции-же!

Тебе не нужен никакой where id select max...

[Elvis]

mayton2019, С новым днем и новыми силами еще поизучал оконные функции, но никак не пойму как же без условия в апдейте, ведь проапдейтятся все записи, что мне не нужно. да и в инете не нашел примеров апдейта с оконными функциями.
С циклом выглядит попроще, но апдейтится 0 строк

DO $$
DECLARE
  arr int[] := array[1792829,1792830,1792831,1792832];
  x int;
BEGIN
  FOREACH x IN ARRAY arr
  LOOP
    update myorders
    set content = jsonb_set(content, '{orders, initialTaskId}', to_jsonb((select t.content #>> '{orders, initialTaskId}' from myorders t where t.id = (select min(t.id) from myorders t where t.ticket_id=x))::text), false)
    where id = (select max(t.id) from myorders t where t.ticket_id=x);
  END LOOP;
END;
$$

[mayton2019]

Забудь про json, и хранимые процедуры.

Твою задачу надо разбить на части. Первая часть. Дано

id    |ticket_id|task_id |
------+---------+--------+
106849|  1792829|14709539|
108057|  1792829|14740625|

Необходимо с использованием оконной функции получить следюущее

id    |ticket_id|task_id |min_task_id_in_ticket
------+---------+--------+----------------------
106849|  1792829|14709539|14709539
108057|  1792829|14740625|14709539

Вот как только ты такой view получишь - реализовать твои апдейты будет делом техники. Туда-же
и добавишь потом фильтрацию по обновлению только нужных.

И никакие под-запросы там будут не нужны.

[Elvis]

mayton2019,

Необходимо с использованием оконной функции получить следюущее

это выглядит не сложно:

select *, min(x.task_id) over (partition by x.ticket_id) from myorders x

[mayton2019]

Elvis, работает?

[Elvis]

mayton2019, select то работает, выводит то значение что нужно, с этим согласен. но я никак не пойму как это использовать в update.
Если сделать так:

update myorders 
set content = jsonb_set(content, '{orders, initialTaskId}', to_jsonb(concat(ticket_id, ':', (select min(x.task_id) over (partition by x.ticket_id) from myorders x where x.ticket_id=ticket_id))::text), false)
where task_id = (select max(t.task_id) from myorders t where t.ticket_id=1792829)

То выдает ошибку

ERROR: more than one row returned by a subquery used as an expression

[mayton2019]

Elvis, забудь. Сделай еще одно поле. По функции Rank или Dens_rank. Оно покажет порядковый номер
таски в группе. Причем тебе надо будет проапдейтить все которые не нулевые.

id    |ticket_id|task_id |min_task_id_in_ticket|Rank
------+---------+--------+------------------------
106849|  1792829|14709539|14709539             | 0
108057|  1792829|14740625|14709539             | 1
108057|  1792829|20000000|14709539             | 2

[Elvis]

mayton2019, оказывается что скрипт с циклом, что я выше писал, работает. просто в результате в dbever показывает что обновлено 0 строк, а на самом деле строки обновляются как нужно. я почему-то даже не подумал проверить.

[mayton2019]

Ну работает и слава богу

[Elvis]

mayton2019, спасибо за уделенное время и за открытие мне оконных функций!

Answer 1

[Elvis]

В итоге вопрос решился так:

DO $$
DECLARE
  arr int[] := array[1792829,1792830,1792831,1792832];
  x int;
BEGIN
  FOREACH x IN ARRAY arr
  LOOP
    update myorders
    set content = jsonb_set(content, '{orders, initialTaskId}', to_jsonb((select t.content #>> '{orders, initialTaskId}' from myorders t where t.id = (select min(t.id) from myorders t where t.ticket_id=x))::text), false)
    where id = (select max(t.id) from myorders t where t.ticket_id=x);
  END LOOP;
END;
$$

Answer 2

[humoured]

select max(t.id)

Comments

[Elvis]

в каком месте?
если про часть "concat", то там нужен не id, а task_id, причем минимальный.
если про условие "where id=...", то да, тут можно так, но сути не меняет, всё равно берется минимальный task_id из всей таблицы.