带有计数器或索引值的Postgres JSONB_ARRAY_ELEMENTS

Question

假设我有这个order表，其中widgets列是一个jsonb数组:

<表类> order_no 部件 tbody><<tr>50(a, b, c)51(d, e, f)

在FROM子句中所属的位置使用set返回函数。然后您可以使用with ordinality选项:

select o.order_no,
w.*
from "order" o
cross join jsonb_array_elements_text(widgets) with ordinality as w(widget, idx)
order by o.order_no, w.idx;

您正在寻找ROW_NUMBER()窗口函数:)

select
order_no,
jsonb_array_elements_text(widgets) widget,
row_number() over(partition by order_no order by order_no) as item_no
from order;