Postgres JSONB_ARRAY_ELEMENTS with counter or index value
Suppose I have this order table, where the widgets column is a jsonb array:
| order_no | widgets |
|---|---|
| 50 | [a,b,c] |
| 51 | [d,e,f] |
select
order_no,
jsonb_array_elements_text(widgets) widget
from order;
| order_no | widget |
|---|---|
| 50 | a |
| 50 | b |
| 50 | c |
| 51 | d |
| 51 | e |
| 51 | f |
Is there a way to get the query to include an increment/counter for each jsonb array element from the record in order? (the example is zero-based, but find with 1-based...)
| order_no | widget | item_no |
|---|---|---|
| 50 | a | 0 |
| 50 | b | 1 |
| 50 | c | 2 |
| 51 | d | 0 |
| 51 | e | 1 |
| 51 | f | 2 |
from Recent Questions - Stack Overflow https://ift.tt/3f8VfBB
https://ift.tt/eA8V8J
Comments
Post a Comment